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.

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.