Search for "LIKE" with "_"

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 "_"

@Dave S — Apparently, you would need to write statements like this one:

LIKE '%child\\_%' ESCAPE '\\'

which actually means that you escaped the underscore character with a back-slash and tell SQLite about it so it uses the true underscore character.

Seen here:

my problem is that contradicts this

http://www.sqlitetutorial.net/sqlite-like/

@Dave S – Yes, but does any of them work? That is the most important

[quote=444397:@Dave S]my problem is that contradicts this

http://www.sqlitetutorial.net/sqlite-like/[/quote]

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.

I tried it, it works.

Perhaps SQLite has never read the documentation.

:stuck_out_tongue:

From SQL Language Expressions

I think this will work:

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).

Mostly works :slight_smile:

except if you enter JUST an “_”

SELECT id_person   FROM viewPEOPLE WHERE givenNAME LIKE '%__%' ESCAPE '_'    OR surNAME LIKE '%__%' ESCAPE '_'    OR nickNAME LIKE '%__%' ESCAPE '_'

this returns ALL records still

if there is at least ONE other character in the pattern it works

Why not use backslash for the escape character?

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” :stuck_out_tongue_winking_eye: you could end with unexpected results.

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)

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 '\\'

no?

@Dave S — If LIKE is not good enough for your case, you should use REGEXP then

LIKE is just fine… and trying to create a REGEx from a realtime user entry would be too complex and not generate a better response

[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

Dave, can you check?