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’:
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?
SELECT * FROM Things WHERE Name LIKE ‘c\_%’ escape ‘\’
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.
And the issue continues…
If the item name contains more than one underscore, I still have a problem. going back to the original list:
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?
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.
Most SQL engines I have worked with are case sensitive…
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
gave zero results
gave >0 results
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.