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.
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';