How to find a string in a sqlite3 table?

Question

Is there a pure sqlite request to find a string in a database ?

With a bit of shell, I can extract the schemas from the tables:

for i in *sqlite; 
   do sqlite3 $i ".tables"
   | grep -Eoh "[^ ]+" 
   | while read t; do 
          echo -n "$i:$t  ";
          sqlite3 $i '.schema "'"$t"'"';
   done;
echo;
done

Then I could find which column has the type TEXT, and make a request.

But is there a pure SQL way to do this in a terminal ?

Brad Parks · Accepted Answer · 2024-03-21 17:32:05Z

3

TLDR; To dump a sqlite db to a file called dump.sql

sqlite3 some.db .dump > dump.sql

SQLite is designed as an embedded database and therefore does have only extremely basic programming capabilities (it has triggers, but no control logic like if or loops). So it is not possible to do anything dynamic in SQL – the result of PRAGMA table_info cannot be directly used in SQL queries.

A very quick and dirty way to search everywhere in a database is to show all contents with .dump and then just grep that. However, this isn't actually quick because searching indexed columns would be faster in SQL.

edited Mar 21, 2024 at 17:32

Brad Parks

73.3k71 gold badges297 silver badges374 bronze badges

answered Oct 31, 2012 at 18:03

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to find a string in a sqlite3 table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related