Search for "LIKE" with "_"

  1. 7 months ago

    Dave S

    6 Jul 2019 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

    6 Jul 2019 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

    6 Jul 2019 Pre-Release Testers 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

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

    I tried it, it works.

  7. Kem T

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

    Perhaps SQLite has never read the documentation.

    :P

  8. Alberto D

    6 Jul 2019 Pre-Release Testers, Xojo Pro
    Edited 7 months 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

    6 Jul 2019 San Diego, California USA
    Edited 7 months 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

    6 Jul 2019 Pre-Release Testers, Xojo Pro, XDC Speakers, MVP Connecticut
    Edited 7 months ago

    Why not use backslash for the escape character?

  11. Alberto D

    6 Jul 2019 Pre-Release Testers, Xojo Pro
    Edited 7 months 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

    6 Jul 2019 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

    6 Jul 2019 Pre-Release Testers, Xojo Pro

    @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

    6 Jul 2019 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

    6 Jul 2019 Pre-Release Testers, Xojo Pro

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