Scott, on Thursday, February 13, 2020 09:01 AM, wrote...
>
> Can I search all tables and columns of SQLite database for a specific
> text string? I'm sure this question has been asked many times, but I'm
> having trouble finding a solid answer.
> My problem: My clients SQLite database has 11 tables and multiple columns
> (some designated and others not) and they want to be able to search the
> entire database for a specific text or phrase.
> What I have done: I've been searching a couple days and found the Full
> Text search on SQLite home based upon using a virtual table, but I don't
> think that will work. It appears that I may be able to search the
> sqlite_master but it seems it may only contain table and column information
> only minus the data.
> What I'm working in: This is an Android app written in Java using the
> SQLite
> What I hope to do: Find a simple query statement or combination of
> statements that may help to efficiently query for the string across tables
> and columns before I resort to multiple queries and methods for all 11
> tables.
> I'm looking for any experienced thoughts or suggestions anyone may have
> encountered resolving this kind of issue if available. I'm not expecting
> anyone to solve it for me -- just some guidance would be helpful.
This is a very wide open question. It is a lot of work to create the query.
I actually have to do this for some tables and some fields, but I know these
tables and these fields. Here are some questions:
1. What are you going to do when you find a string match in a table field?
2. Do you need to know that table? Do you need to know the field?
3. Do you need the whole content of that field if matched?
There are just too many questions to help, but it is possible if you know
what do you want to do. Here are some ideas:
a. The command prompt has a .table option that will provide all the tables
available on a DB
b. The .schema [tablename] will give you the table's fields
Imagine these three tables:
create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c, t0d);
insert into table0 (t0a, t0b, t0c, t0d) values ('text in here', 'Shelby 2002',
'2 plus 2 equals 4', 'I am going home soon');
create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12);
insert into table1 (t10,t11,t12) values ('p001', 'Shelby 2002', '1 plus 1
equals 2');
create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c, t2d);
insert into table2 (t2a, t2b, t2c, t2d) values ('in here', 'going home',
'2020-02-11','Once upon a time...');
SELECT
'field t0a on table0 has the string [plus]: ', t0a from table0
WHERE t0a LIKE '%plus%'
UNION
SELECT
'field t0b on table0 has the string [plus]: ', t0b from table0
WHERE t0b LIKE '%plus%'
UNION
SELECT
'field t0c on table0 has the string [plus]: ', t0c from table0
WHERE t0c LIKE '%plus%'
UNION
SELECT
'field t0d on table0 has the string [plus]: ', t0d from table0
WHERE t0d LIKE '%plus%'
UNION
SELECT
'field t10 on table1 has the string [plus]: ', t10 from table1
WHERE t10 LIKE '%plus%'
UNION
SELECT
'field t11 on table1 has the string [plus]: ', t11 from table1
WHERE t11 LIKE '%plus%'
UNION
SELECT
'field t12 on table1 has the string [plus]: ', t12 from table1
WHERE t12 LIKE '%plus%'
UNION
SELECT
'field t2a on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
UNION
SELECT
'field t2b on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
UNION
SELECT
'field t2a on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
;
For just to search on the string 'plus' you will have to do the
above query.
sqlite> SELECT
...> 'field t0a on table0 has the string [plus]: ', t0a from table0
...> WHERE t0a LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t0b on table0 has the string [plus]: ', t0b from table0
...> WHERE t0b LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t0c on table0 has the string [plus]: ', t0c from table0
...> WHERE t0c LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t0d on table0 has the string [plus]: ', t0d from table0
...> WHERE t0d LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t10 on table1 has the string [plus]: ', t10 from table1
...> WHERE t10 LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t11 on table1 has the string [plus]: ', t11 from table1
...> WHERE t11 LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t12 on table1 has the string [plus]: ', t12 from table1
...> WHERE t12 LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t2a on table2 has the string [plus]: ', t2a from table2
...> WHERE t2a LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t2b on table2 has the string [plus]: ', t2a from table2
...> WHERE t2a LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t2a on table2 has the string [plus]: ', t2a from table2
...> WHERE t2a LIKE '%plus%'
...> ;
field t0c on table0 has the string [plus]: |2 plus 2 equals 4
field t12 on table1 has the string [plus]: |1 plus 1 equals 2
sqlite>
So, it's possible, and it you use the .tables and .schema, you can use
java to create these very LOOOOOOONG query. :-) Lots of repetitious
strings and selects. This is the beginning. :-) I hope this helps.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
