Searching in Sqlite with a ; char in the search term

[andyh1234]

After three hours of trying, and web searching I'm finally caving in and posting here in case anyone has a clue!!

How do you search for something with a ; in the title in sqllite?

For example I have a database with 'Joes; Cafe' in one of the fields.

I can get the data out by using

select titles from mytable where titles like '%Joes%';

But, if I want to include the ; it fails, even if I escape it e.g. the following will return no results,

select titles from mytable where titles like '%Joes\; Cafe%';

Its probably my limited sql knowledge, but I'm hitting a total brick wall with this.

Andy

[KennyR]

I will attempt to help out a fellow SQLite head basher since I messed with this ALL last night! Hope this helps....

Now I used the DISTINCT term to just search one column of my database....you can remove the DISTINCT if you want to return the entire row of data (I think). But if you play with this code a bit and substitute your own database name and columns, it should work fine. When I do a search with this function, I can return all the entries for a particular letter, like "A". I was going to work today on figuring out how to find data in the database when the second word of the entry is searched...(like an entry "Good Day") when I search "Good". Right now, it will only return searches with the database entries first word "Good" I hope this helps in just the slightest! Ill check back and see if I need to clarify...or someone who knows what their doing can comment!

PS. The variable '" & vSearch & "%' refers to the text placed in that variable when a user clicks the "Search" button. You can put 'Joes Cafe' in its place to search for a specific string of text. I like to put everything in variables and search that way....I assume that is what you want to do...You can accomplish this by coding a button for your search function like so...

on mouseUp
local vSearch
ask "Enter a search term"
put it into vSearch
put databaseGetContactDetailsRecordsAdmin() into fld "Field"
end mouseUp

Code: Select all

function databaseGetContactDetailsRecordsAdmin
   ## Query the database for contact details to be displayed in the field
   put getDatabaseID() into tDatabaseID
   local vSearch
   put "SELECT DISTINCT name FROM Inventory WHERE name Like  '" & vSearch & "%';" into tSQL
   
   put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
   return tRecords
end databaseGetContactDetailsRecordsAdmin

OR

Code: Select all

function databaseGetContactDetailsRecordsByName
   ## Query the database for contact details to be displayed in the field
   put getDatabaseID() into tDatabaseID
   global vSearch
   put "SELECT * from Inventory where name Like '" & vSearch & "%';" into tSQL
   put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
   return tRecords
end databaseGetContactDetailsRecordsByName

[andyh1234]

Thanks Kenny, Ill give it a try!

Andy

[dave_probertGA6e24]

Hi Andy

I'd be interested in the results of KennyR's code - does it help you with your semi-colon problem?

I don't see how it would if the SQL string is failing at the db call part - how the string is constructed shouldn't really matter!

For SQL data via LC I've taken to 'sanitizing' the data going into certain fields - if they contain 'return's or 'tab's I replace them (with special characters or something - I use ':CR:' and ':TB:') before writing to the database, then they don't mess with the return and tab separated data coming back. When I need to use the data I simply run an 'unsanitize' function to change the :CR: back to a return, etc. This allows me to not worry about strange rows/columns appearing when user data is entered and saved. You could try a similar approach with the semi-colon - i.e. replace it with :SC: (or something) before writing, and have any searches use that string instead of the actual semi-colon character. It might work for your situation - or might incur too much overhead of messing around with the data in code. I should mention that the 'sanitise' and 'unsanitize' functions are my own ones - not part of LC.

Cheers,
Dave

[andyh1234]

Yes, unfortunately it didn't work, and in this case I have to use an existing database that has semi colons in it, otherwise the replacement menthol would be a great answer.

The ; is interpreted by the engine as a termination character for the line so it causes an sql error, and if I escape it using \; the sql works, but won't find anything containing a ;, like the \ is actually removing it from the search string.

Ive tried replacing it with both a % and a _ to try to get a wildcard search but no joy.

Im running through all the rev db commands one by one now to see if any will give a result, if not Ill have to see if I can get the database updated per your initial suggestion as that might be the only way to get this to work.

Unfortunately I'm out of my depth with sql and was hoping it was something I was doing wrong, there must be a way to search for a string containing a character used by sqlite!

[andyh1234]

Got there in the end!

I had to use the following (or at least the following works for me...)

put "SELECT titles FROM mytable WHERE (titles LIKE :1 ESCAPE '\')" into tSQL
put tidySQL("Joes\; Cafe") into tSearchTerm
put revdb_querylist(,,gDatabaseHandles["myDB"],tSQL,"tSearchTerm") into tList


function tidySQL term
replace "'" with "\'" in term
replace "\" with "\\" in term
replace ";" with "\;" in term
replace numtochar(13) with empty in term
return term
end tidySQL

[dave_probertGA6e24]

Nice. I didn't know about the ESCAPE command in sql - might have to use that a bit !

Well, I hope we both find out more about SQLite and it's LC interface (though I think the latter side is pretty straightforward)

Maybe someone should work out a little library of useful helper routines to use with the main ones.

Have fun,
Dave

[KennyR]

And this would be why I included the "Someone who knows" part of my original post. I should stick to being a paramedic! lol....sorry it did not work for you...The way I have my database constructed it works "okay" for me...but I am a beginner level programmer....Glad you got it figured out....

[dave_probertGA6e24]

Sorry Kenny, I had to comment because of what the original message was about - your answer didn't really target the 'semi-colon' problem

I'm not really someone who knows - only someone who tries to help - really the same as yourself

But my day-job is also as a programmer - really not as useful as yours. No=one calls a programmer in an medical emergency!!!

Cheers,
Dave

[KennyR]

Dave,
Please don't take my post as me being upset! I was just poking fun at myself...I wish I had the know how most of the good people on here seem to have....every day is a new learning experience for me....I love working in LC and wish I knew more so I could help people out...have a good one!