SQLite search within a string

[jfxwave]

I have been looking around on the forum for about 2 hours and didn't find what i need help with.

I'm needing help in finding out how to do a search inside my DB within a string.

Exp: If i have a Table called "HELP" and one of the column is named "Notes" and ID 1 under "Notes" it = "The mouse jumped over the clock"

How would i search inside all rows where "Notes" located for the word "mouse" and if sql finds one it gives me the ID.

I kinda need a search like the one this site uses.

Thanks for any help.

[azmanar]

Hi,

You can study the method used under Roboblue's Customer DB App located at this examples site (http://azman.info/ams).

In the APZ file, look for the search button and double click to see its code.

You might want to use something similar to this. It might work.:

--Your variable that may come from input box named MySearch
sSearch = Input.GetText("MySearch");
if sSearch ~= "" then

--This will output a table for the ID you're looking for:
tblMySearch = SQLite.QueryToTable(db, "Select ID,NOTES from HELP where (NOTES LIKE '%"..Input.GetText("MySearch").."%') ;

if tblMySearch then
--This will output your results to a ListBox:
for nRow = 1,tblMySearch.Rows do
--add the item to the listbox called LB_Results
ListBox.AddItem("LB_Results", tblMySearch.Data[nRow]["ID"], tblMySearch.Data[nRow]["NOTES"]);
end -- endForLoop

else

Dialog.Message("Notice", "SEARCH NOT FOUND", MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1);
end -- if tblMySearch

end -- if sSearch not empty

[jfxwave]

Thanks allot! :)

I came up with this from your help

ListBox.DeleteItem("ListBox1", -1);
--Your variable that may come from input box named MySearch
sSearch = Input.GetText("InputSearch");
if sSearch ~= "" then
OpenHelpDatabase();
--This will output a table for the ID you're looking for:
tblMySearch = SQLite.QueryToTable(Hdb, "Select ID,Details,Notes from HELP where (Details LIKE '%"..Input.GetText("InputSearch").."%')") ;

if tblMySearch then
--This will output your results to a ListBox:
for nRow = 1,tblMySearch.Rows do
--add the item to the listbox called LB_Results
ListBox.AddItem("ListBox1", tblMySearch.Data[nRow]["ID"] .. ", " ..tblMySearch.Data[nRow]["Details"], tblMySearch.Data[nRow]["Notes"]);
end -- endForLoop
end
CloseHelpDatabase();
else

Dialog.Message("Notice", "SEARCH NOT FOUND", MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1);

end -- if sSearch not empty

and it works perfect.

Is there a way for me to search for more then one word at a time?

Like i could type in "mouse clock" and have it pick string that contains both words.