Odd SELECT issue in SQLite

  1. 5 years ago

    Dale A

    18 Dec 2014 San Diego, California, USA

    I don't know if I'm seeing a bug in SQLite or if it just acting in a way I don't understand. I do know it isn't a Xojo bug but I'm asking about it here since trying to find anything in SQLite documentation is, for me, frustrating.

    I have a database that has the following (excerpted) set of rows in the table called 'Things':

    ID     Name
    1      Crux
    2      Critic
    3      C_Calite

    If I issue a select statement like

    SELECT * FROM Things WHERE Name LIKE 'c%'

    I get all three rows returned as I would expect. If, however, I issue a select statement like

    SELECT * FROM Things WHERE Name LIKE 'c_%'

    I still get all three rows. I expected to get only the third row since it is the only one with the underscore in it.

    Is that normal behaviour? If so, is there a "trick" to getting the SELECT statement to distinguish the rows with the underscore?

  2. Norman P

    18 Dec 2014 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...

    escaping
    try
    SELECT * FROM Things WHERE Name LIKE 'c\_%' escape '\'

  3. Dale A

    18 Dec 2014 San Diego, California, USA

    Thanks, Norman, that works. Now I'm going to have to figure how to incorporate that into some old code in a few dozen places.
    Well if it were easy we wouldn't drive ourselves crazy with it.

  4. Dale A

    18 Dec 2014 San Diego, California, USA

    And the issue continues...

    If the item name contains more than one underscore, I still have a problem. going back to the original list:

    ID     Name
    1      Crux
    2      Critic
    3      C_Calite
    4      C_Calite_Hyper

    I need to be able to pull back item 4 by name. If I try

    select name from things where name like 'c\_calite\_ hy%' ESCAPE '\'

    I get no error but also no returned value. So I am apparently not getting the syntax right.

    On a different take, would this work easier as a preparedstatement? Would that get around the issue?

  5. Scott G

    18 Dec 2014 Pre-Release Testers, Xojo Pro

    if that's your actual code you've got a space between \_ and hy%

  6. Dale A

    19 Dec 2014 San Diego, California, USA

    @Scott G if that's your actual code you've got a space between \_ and hy%

    No. That was a type in the posting. The actual code does not have that space.

  7. Dave S

    19 Dec 2014 San Diego, California USA

    Most SQL engines I have worked with are case sensitive....

  8. Norman P

    19 Dec 2014 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...

    like usually isn't unless of course you use postgresql then you have to worry about the difference between LIKE an ILIKE

  9. Dave S

    19 Dec 2014 San Diego, California USA

    @Norman P like usually isn't unless of course you use postgresql then you have to worry about the difference between LIKE an ILIKE

    well ORACLE is .... I just tested it

    LIKE 'm%'

    gave zero results

    LIKE 'M%'

    gave >0 results

  10. Dale A

    19 Dec 2014 San Diego, California, USA

    Whenever I use LIKE in SQL, unless I'm looking for case-dependencies, I always explicitly lowercase the values in the comparisons, just in case (pun intended).

    I'm going to close this down. I decided that the way I was approaching the problem wasn't the best way so I scrapped it and rewrote the whole shebang. Now, the problem is gone and the routines are faster than before.

    Thanks to all and have a Happy Holiday, whichever one(s) you celebrate.

or Sign Up to reply!