LIKE and SQLite

[AlpVir]

Suppose we have four records and a two field, "Number" and "Name".
"1" , "Jeffers Hurd Howe Finn"
"2" , "Fower Howe Kean Finn Douglas Gage Stein"
"3" , "Harrison Jefferson Finn"
"4" , "Irwin Bukean Tagore Howe"

Instruction SQLite .... Name LIKE '% "& N & "%' .....
if N = "Finn" returns 1, 2 and 3
if N = "Hurd" returns 1
if N = "Harrison" returns 3
but if N = "Jeffer" returns 1 and 3 instead of returning only 1.
Furthermore, if N = "Kean" returns 2 and 4 instead of only 2.

There is a solution to this?
Thanks for your attention.

 

[pluton]

If you want exactly only one field return use Like but then you must provide correct name.

Example: Google

Like Google = return Google
Like Goo = return nothing
Like %Goo% = return all with goo letters in it.

I didn't find any other solution because Like % .. % is used to find all words containing like letters

 

[Merlot2309]

Hello,

This might help you as well: SQL LIKE Operator

Success,
Helen.

 

[AlpVir]

It will not be the most efficient but it works for me

'  Nom is the name to search
C = " " & rs.GetString("CA14").Trim & " "
Dim Rows() As String
Dim Trovato as Boolean
Rows = Regex.Split("[ ]",C)
Trovato = False 
For i = 0 To Rows.Length - 1
   If Rows(i).Trim = Nom Then Trovato= True 
Next
If Trovato = True Then 
....................

 

[Caravelle]

Instruction SQLite .... Name LIKE '% "& N & "%' .....

This means select anything containing N, and that is what you are getting.

The % symbol represents any number of characters (including none). You have one before your search term and one after, and it appears that the second one is superfluous for what you want to achieve.

Caravelle

 

[Caravelle]

Looking again at what you say, it seems that you only want to return exact matches of words which may have a space at the beginning or at the end or both.

How about:

N1 = " " & N
N2 = N & " "

Then your SELECT query could be WHERE NAME = N1 OR NAME = N2

Just an idea...

Caravelle

 

[AlpVir]

Thanks "Caravelle"
but the field is UNIQUE, not TWO as suggested by you.
The solution I found is perfect, but "inelegant".

 

[Caravelle]

Sorry, I do not understand at all what you mean.

Caravelle.