Can I search all tables and columns of SQLite database for a specific text string?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Can I search all tables and columns of SQLite database for a specific text string?

Scott
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.
Thanks,
Scott ValleryEcclesiastes 4:9-10
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I search all tables and columns of SQLite database for a specific text string?

Thomas Kurz
I would create an SQL dump ("sqlite3 file.db .dump") and search therein.


----- Original Message -----
From: Scott <[hidden email]>
To: SQLite Mailing List <[hidden email]>
Sent: Thursday, February 13, 2020, 15:01:06
Subject: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?

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.
Thanks,
Scott ValleryEcclesiastes 4:9-10
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I search all tables and columns of SQLite database for a specific text string?

Simon Slavin-3
In reply to this post by Scott
On 13 Feb 2020, at 2:01pm, Scott <[hidden email]> wrote:

> Can I search all tables and columns of SQLite database for a specific text string?

No.  There's no way to do this, and there's no way to say "all tables" in SQL.  In other words it's not easy to write such a thing.

I like Thomas Kurz's solution, to dump the database as SQL commands.  Alternatively you could write a text exporter for all the columns you think might hold the string, then search that text.

If you want better solutions, it might help to tell us whether this is a one-time problem, something you (the developer) might want to do repeatedly, or something you want the user to be able to do repeatedly.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I search all tables and columns of SQLite database for a specific text string?

Scott
Tom and Slavin:
The dump of information sounds like a good idea. To Slavin's question, the user need to be able to repeated search, but as a developer, I would want and idea I can eventually implement repeatedly. I've done this successfully in the past, but it required 4-5 methods handling a combination of SQL statements and looping arrays.
Thanks for your help guys!
Scott ValleryEcclesiastes 4:9-10

    On Thursday, February 13, 2020, 09:35:54 AM EST, Simon Slavin <[hidden email]> wrote:  
 
 On 13 Feb 2020, at 2:01pm, Scott <[hidden email]> wrote:

> Can I search all tables and columns of SQLite database for a specific text string?

No.  There's no way to do this, and there's no way to say "all tables" in SQL.  In other words it's not easy to write such a thing.

I like Thomas Kurz's solution, to dump the database as SQL commands.  Alternatively you could write a text exporter for all the columns you think might hold the string, then search that text.

If you want better solutions, it might help to tell us whether this is a one-time problem, something you (the developer) might want to do repeatedly, or something you want the user to be able to do repeatedly.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I search all tables and columns of SQLite database for a specific text string?

Jose Isaias Cabrera-4
In reply to this post by Scott

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I search all tables and columns of SQLite database for a specific text string?

John Gillespie-2
You can do it in Tcl (reusing Jose's example tables) like this:

package require sqlite3
sqlite3 dbcmd ~/tmp/grbg.db

 dbcmd eval "create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c,
t0d)"
 dbcmd eval "insert into table0 (t0a, t0b, t0c, t0d) values ('text in
here', 'Shelby 2002', '2 plus 2 equals 4', 'I am going home soon')"
 dbcmd eval "create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12)"
 dbcmd eval "insert into table1 (t10,t11,t12) values ('p001', 'Shelby
2002', '1 plus 1 equals 2')"
 dbcmd eval "create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c,
t2d)"
 dbcmd eval "insert into table2 (t2a, t2b, t2c, t2d) values ('in here',
'going home', '2020-02-11','Once upon a time...')"

 set searchstr "plus"
 set SQL ""
 set u ""
 set tables [dbcmd eval "select name from sqlite_master where type='table'"]
 foreach t $tables {
   dbcmd eval "pragma table_info($t)" {
    # cid name type notnull dflt_value pk
    if {[regexp -nocase -- {char|text} $type] || $type == ""} {
      append SQL "$u select '$t' as tn, '$name' as cn, $name as val from $t
\
         where $name like '%$searchstr%' "
           set u "union"
    }
    if {[string length $SQL] > 1000000} {
        error "too many fields"
    }
   }
 }
 dbcmd eval $SQL {
   puts [format "Field %-15s on %-15s has the string %-15s: %s"  $cn $tn
$searchstr $val]
 }
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

John G


On Thu, 13 Feb 2020 at 15:24, Jose Isaias Cabrera <[hidden email]>
wrote:

>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users