Odd SELECT issue in SQLite

Databases
1

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

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

3

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

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

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

6

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

7

Most SQL engines I have worked with are case sensitive…

8

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

9

well ORACLE is … I just tested it

LIKE 'm%'

gave zero results

LIKE 'M%'

gave >0 results

10

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.