Search for "LIKE" with "_"

  1. 7 weeks ago

    Dave S

    Jul 6 San Diego, California USA

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

    @Kem T Why not use backslash for the escape character?

    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
  2. @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:

  3. Dave S

    Jul 6 San Diego, California USA

    my problem is that contradicts this

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

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

  5. Tim S

    Jul 6 Canterbury, UK

    @Dave S my problem is that contradicts this

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

    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.

  6. Kem T

    Jul 6 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    I tried it, it works.

  7. Kem T

    Jul 6 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    Perhaps SQLite has never read the documentation.

    :P

  8. Alberto D

    Jul 6 Pre-Release Testers
    Edited 7 weeks ago

    From https://www.sqlite.org/lang_expr.html

    If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. 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.

    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 Stéphane 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).

  9. Dave S

    Jul 6 San Diego, California USA
    Edited 7 weeks ago

    @Tim S you need to double up your underscores and tell it that the underscore is the escape char.

    Mostly works :)

    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

  10. Kem T

    Jul 6 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut
    Edited 7 weeks ago

    Why not use backslash for the escape character?

  11. Alberto D

    Jul 6 Pre-Release Testers
    Edited 7 weeks ago

    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" ;P you could end with unexpected results.

  12. Dave S

    Jul 6 Answer San Diego, California USA

    @Kem T Why not use backslash for the escape character?

    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
  13. Alberto D

    Jul 6 Pre-Release Testers

    @Dave S because then is doesn't find "child_\9"

    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?

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

  15. Dave S

    Jul 6 San Diego, California USA

    @Stéphane ;Mons @Dave Sisemore — 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

  16. Alberto D

    Jul 6 Pre-Release Testers

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

    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?

or Sign Up to reply!