sqlite db search full table for a string

Databases
1

Hi,

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 ?

2

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.

3

You could try a full-text search:

http://www.xojo.com/blog/en/2014/03/full-text-search-with-sqlite.php

4

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

5

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

6

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…

7

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.

8

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.

9

Thanks. Could be helpful.