Search across all columns in a table

full-text-searchsqlitewhere

As the title says, I want to create a query that searches a keyword across all columns in that table.

So I want to simplify this query:

SELECT * FROM tblCustomers
    WHERE (col1 LIKE '%keyword%' OR col2 LIKE '%keyword%' OR col3 LIKE '%keyword%' OR col4 LIKE '%keyword%') 
    AND (col1 LIKE '%keyword2%' OR col2 LIKE '%keyword2%' OR col3 LIKE '%keyword2%' OR col4 LIKE '%keyword2%');

To something like:

SELECT * FROM tblCustomers
    WHERE (allColumns LIKE '%keyword%') 
    AND (allColumns LIKE '%keyword2%');

Best Answer

Full Text Search

You can use FTS5

CREATE VIRTUAL TABLE customer USING tblCustomers(col1, col2, col3);

Then you just

SELECT * FROM tblCustomers WHERE email MATCH 'keyword AND keyword2';