Real Software Forums
http://forums.realsoftware.com/

A little problem with SQLite and the LIKE statement
http://forums.realsoftware.com/viewtopic.php?f=3&t=47940
Page 1 of 1

Author:  HMARROQUINC [ Tue May 21, 2013 7:36 pm ]
Post subject:  A little problem with SQLite and the LIKE statement

I'm attempting to do a search based on the user's input but have a little problem with the following SQL statement:

SELECT * FROM Products WHERE DescriptionShort LIKE '%let''s%' OR ProductCode LIKE '%let''s%' AND CompanyID = 1 ORDER BY DescriptionShort ASC ;


When Saving the record to the database I'm escaping the single quote to double quotes so in fact the db is storing the word -let''s- but for some reason the above query does not seem to find anything.

I have run the query in a db manager and of course it doesn't return anything either. I'm a bit confused.

Any ideas on what I'm doing wrong?

Author:  timhare [ Tue May 21, 2013 7:41 pm ]
Post subject:  Re: A little problem with SQLite and the LIKE statement

Is the data really in the database with 2 quote marks? If so, how did you get it stored that way? If you say

update sometable set somecolumn = 'let''s'

the data will be stored as let's, with a single quote. If you use as prepared statement, then you shouldn't be escaping the quote, as it will be stored verbatim. So if you escape a single quote and then store it via prepared statement, you will get 2 quote marks in the data: let''s. Is that what happened?

If the data really has 2 quote marks (let''s), then you need to escape both of them:

where productcode like 'let''''s'

(4 ' marks = 2 ' marks in the data)

Author:  HMARROQUINC [ Tue May 21, 2013 7:46 pm ]
Post subject:  Re: A little problem with SQLite and the LIKE statement

Thanks for your answer Tim. This comes directly from the database, of course it's just dummy data

This is the short description for product 1, Let''s test for single quotes.


That's what's being stored in the database.

I was testing to make sure the search for a word with single quote was working and nope, double quotes seem to be working properly.

Author:  HMARROQUINC [ Tue May 21, 2013 7:52 pm ]
Post subject:  Re: A little problem with SQLite and the LIKE statement

Thanks Tim, Using 4 quotes did the trick. Don't know why I missed that but your help was invaluable.

Author:  timhare [ Tue May 21, 2013 8:01 pm ]
Post subject:  Re: A little problem with SQLite and the LIKE statement

There is some bug somewhere that is storing the text with 2 quote marks. At least I assume that isn't what you intended to do.

Author:  HMARROQUINC [ Tue May 21, 2013 8:06 pm ]
Post subject:  Re: A little problem with SQLite and the LIKE statement

Actually I am storing the text like that, escaping the single quote. For cases like O'Brian or something similar. I used the word let's just as something that popped in my head when testing but the real intention is to handle single quotes.

I know... Prepared statements... But this is just a quick prototype and the SQL will get replaced with prepared statements.

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/