How to search for a single value in multiple columns?-CodePudding

I'm implementing a search-bar for my program that will query a database based on user input. When the user enters a single search term with spaces, example: I am searching for something, the program takes it as space separated values and performs a search for each value in multiple relevant columns in the database. An example query (based on the above search phrase) would be:

SELECT * FROM TableName WHERE (
  col1 LIKE "%I%" OR col2 LIKE "%I%" OR col3 LIKE "%I%" OR col4 LIKE "%I%" OR col5 LIKE "%I%"
OR col1 LIKE "%am%" OR col2 LIKE "%am%" OR col3 LIKE "%am%" OR col4 LIKE "%am%" OR col5 LIKE "%am%"
)

and so on for each space separated value in the input. As you can expect, this will be a very long query based on user input.
My question is, is there a better way to search for a single value in multiple columns? Or just a better way to implement a search like this one.

CodePudding user response：

Yes, SQLite provides full-text search functionality via the FTS5 module. Firstly you need to create a virtual table:

CREATE VIRTUAL TABLE virtual_table USING fts5(col1,col2,col3,col4,col5);

Note that you can not add types, constraints or PRIMARY KEY declarations to a CREATE VIRTUAL TABLE statement used to create an FTS5 table.

Populate your table using INSERT (UPDATE/DELETE) like any other table:

INSERT INTO virtual_table (col1, col2, col3, col4, col5)
VALUES ('I', 'like', 'doughnuts', 'with', 'chocolate'),
       ('I', 'am', 'searching', 'for', 'something'),
       ('We', 'are', 'going', 'to', 'the park');

Then you can use the full power of full-text search functionality. There are three ways to execute a full-text query, see the documentation for more details. One possible option would be using the MATCH operator:

SELECT *
FROM virtual_table
WHERE virtual_table MATCH 'I am searching for something';