Re: [sqlite] 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] > 100} {
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
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 tab
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
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 wrote: On 13 Feb 2020, at 2:01pm, Scott 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
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 st
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
On 13 Feb 2020, at 2:01pm, Scott 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
I would create an SQL dump ("sqlite3 file.db .dump") and search therein.
- Original Message -
From: Scott
To: SQLite Mailing List
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
