Full-text-search can be added to SQLite by using the fts5 extension.

Assuming we have a table like so:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE tickers (
	id INTEGER PRIMARY KEY,
	symbol TEXT UNIQUE,
	name TEXT,
	clean_name TEXT,
	description TEXT,
	sector TEXT,
	industry TEXT,
	country TEXT,
	tags TEXT,
	created_at TEXT DEFAULT CURRENT_TIMESTAMP,
	updated_at TEXT	DEFAULT CURRENT_TIMESTAMP
);

To enable the full-text-search we need to create a virtual table using the FTS5 extension:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE VIRTUAL TABLE fts_tickers USING FTS5(
	-- let's add the id for reference without indexing it for fast-text-search
	id UNINDEXED, 
	symbol, 
	name, 
	clean_name, 
	description, 
	sector, 
	industry, 
	country,
	tags
)

We can then index the data on the full-text-search table by using triggers or for example inserting from the source table directly:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

INSERT INTO fts_tickers(
	name, 
	symbol, 
	clean_name, 
	sector, 
	industry, 
	country, 
	description, 
	tags
) SELECT id, name, symbol, clean_name, sector, industry, country, description, tags FROM tickers

Querying can then be performed by matching all the columns indexed:

1

SELECT * FROM fts_tickers WHERE fts_tickers MATCH 'ev'

Or a specific column:

1

SELECT * FROM fts_tickers WHERE name MATCH 'ev'

We can order it by rank, ie, the closest matches will appear at the top, eg:

1

SELECT * FROM fts_tickers WHERE fts_tickers MATCH 'ev' ORDER BY rank