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

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