sqlite db search full table for a string


I have a database with 9 columns minimum and about 1000 rows. I need to
lookup the complete table for a specific string. At the moment I do it like below
but I have a feeling that this could be easier and faster:

I tried to find the answers in the sqlite manuals but suffering from examples and to be honest I am
pretty new teh database stuff.

Can you guys help me ?

Thats correct.

You might add an extra search field that is the concatenation of all the fields, so that your search can happen on a single field.
Or you can use concat

Select * from clients where Concat(field1, ‘’, field2, ‘’, fieldn) like “%Mary%”

but you have to wrap fields that could be null : IFNULL or COALESCE could be used to turn nulls into empty strings.

You could try a full-text search:


That kinda emulates the “add an extra search field which is the concatenation of all the fields” approach in a ‘supported’ fashion.

FTS is pretty cool once you wrap your head around it.

While FTS is very powerful, my guess is that with a table of 1000 rows, and assuming that the columns do not contain hugely large strings, your original query will be blazingly fast…

Thanks for answers.

Yes, the inquiry is fast but building the sqlite string with concateing the string seems
like “unprofessianl”. I also think it’s good to understand the sqlite engine and how powerful it is. I will give
the FTS a try through. That’s what I found in the internet too but at the first glance it seems a bit difficult
to understand. -> “wrap your head around” :slight_smile:

Maybe this knwolege will help me later on.

You can use the Database.FieldSchema to get the field list, and dynamically build the select string. Make this a global method, or extend the Database class, and you could use it on any table/database.

Thanks. Could be helpful.