I have a database of “people” and have a control that searches with a live listbox update as the user enters a search criteria
Some of the data has “_” in it… a character which is part of the SQLite “LIKE” pattern
According to the documention… you need to add an “ESCAPE” clause, but I guess I’m doing it wrong,
Here is a sequence of SQL statements that are generated as the user types… the all return results EXCEPT that last one (and there ARE “CHILD_1”, “CHILD_2” records
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%c%' OR surNAME LIKE '%c%' OR nickNAME LIKE '%c%'
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%ch%' OR surNAME LIKE '%ch%' OR nickNAME LIKE '%ch%'
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%chi%' OR surNAME LIKE '%chi%' OR nickNAME LIKE '%chi%'
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%chil%' OR surNAME LIKE '%chil%' OR nickNAME LIKE '%chil%'
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%child%' OR surNAME LIKE '%child%' OR nickNAME LIKE '%child%'
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%child_%' ESCAPE "_" OR surNAME LIKE '%child_%' ESCAPE "_" OR nickNAME LIKE '%child_%' ESCAPE "_"
I don’t think that is correct. Reading the SQLite doc at https://www.sqlite.org/lang_expr.html, the section entitled “The LIKE, GLOB, REGEXP, and MATCH operators”, you either do it as Stephane describes or you need to double up your underscores and tell it that the underscore is the escape char.
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%child__%' ESCAPE "_" OR surNAME LIKE '%child__%' ESCAPE "_" OR nickNAME LIKE '%child__%' ESCAPE "_"
beside what Stphane wrote:
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%child\\_%' ESCAPE "\" OR surNAME LIKE '%child\\_%' ESCAPE "\" OR nickNAME LIKE '%child\\_%' ESCAPE "\"
Edit: basically what Tim said (double up underscore with Escape underscore).
I guess by them writing: “The escape character followed by a percent symbol (%), underscore (_), or a second instance of the escape character itself matches a literal percent symbol, underscore, or a single escape character, respectively.” is their way to say “don’t use the percent symbol or underscore as a single escape character” you could end with unexpected results.
this seems to cover most situations (except if search is only multiple “_”, but that is a super edge case for me)
If searchPATTERN<>"" Then
Dim pattern As String="'%"+searchPATTERN+"%'"
If searchPATTERN<>"_" Then
If InStr(searchPATTERN,"_")>0 Then
pattern=ReplaceAll(pattern,"_","__")
pattern=pattern+" ESCAPE '_'"
End If
Else
pattern="'%\\_%' ESCAPE '\\'"
End If
SQL=SQL+ _
" WHERE givenNAME LIKE "+pattern+_
" OR surNAME LIKE "+pattern+_
" OR nickNAME LIKE "+pattern
debug sql
End If
[quote=444409:@Dave S]because then is doesn’t find “child_\9”
[/quote]
For “child_\9” this should work:
SELECT id_person FROM viewPEOPLE WHERE givenNAME LIKE '%child\\_\\\\%' ESCAPE '\\' OR surNAME LIKE '%child\\_\\\\%' ESCAPE '\\' OR nickNAME LIKE '%child\\_\\\\%' ESCAPE '\\'
[quote=444409:@Dave S]because then is doesn’t find “child_\9”
this seems to cover most situations (except if search is only multiple “_”, but that is a super edge case for me)
[/quote]
I think this code will find “child_\9” with searchPATTERN = “" will create "%\\\%” and also the case when is only multiple “", will create "%\\_%”
If searchPATTERN<>"" Then
Dim pattern As String="'%"+searchPATTERN+"%'"
If InStr(pattern,"\")>0 Then
pattern=ReplaceAll(pattern,"\", "\\\")
End
If InStr(pattern,"_")>0 Then
pattern=ReplaceAll(pattern,"_","\\_")
End
If InStr(pattern,"\")>0 Then
pattern=pattern+" ESCAPE '\\'"
End
SQL=SQL+ _
" WHERE givenNAME LIKE "+pattern+_
" OR surNAME LIKE "+pattern+_
" OR nickNAME LIKE "+pattern
debug sql
End If