New topic: A little problem with SQLite and the LIKE statement
<http://forums.realsoftware.com/viewtopic.php?t=47940> Page 1 of 1 [ 6 posts ] Previous topic | Next topic Author Message HMARROQUINC Post subject: A little problem with SQLite and the LIKE statementPosted: Tue May 21, 2013 7:36 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 573 Location: Guatemala, Central America 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? _________________ Future RS guru. Ride the world! Top timhare Post subject: Re: A little problem with SQLite and the LIKE statementPosted: Tue May 21, 2013 7:41 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12361 Location: Portland, OR USA 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) Top HMARROQUINC Post subject: Re: A little problem with SQLite and the LIKE statementPosted: Tue May 21, 2013 7:46 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 573 Location: Guatemala, Central America 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. _________________ Future RS guru. Ride the world! Top HMARROQUINC Post subject: Re: A little problem with SQLite and the LIKE statementPosted: Tue May 21, 2013 7:52 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 573 Location: Guatemala, Central America Thanks Tim, Using 4 quotes did the trick. Don't know why I missed that but your help was invaluable. _________________ Future RS guru. Ride the world! Top timhare Post subject: Re: A little problem with SQLite and the LIKE statementPosted: Tue May 21, 2013 8:01 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12361 Location: Portland, OR USA 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. Top HMARROQUINC Post subject: Re: A little problem with SQLite and the LIKE statementPosted: Tue May 21, 2013 8:06 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 573 Location: Guatemala, Central America 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. _________________ Future RS guru. Ride the world! Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 6 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
