Sqllite FTS5: Substring matching using Trigram Tokenizer

Question

I'am thinking of using Trigram Tokenizer to support substring matching in FTS.

Reference: https://sqlite.org/fts5.html#the_experimental_trigram_tokenizer

I created a dummy table and data as given below:

CREATE VIRTUAL TABLE tri_test USING fts5(a, tokenize="trigram");
INSERT INTO tri_test VALUES('abcde\fghij KLMNOPQRST uvwxyz');

However, when I do a select query with search text having non alphanumeric character such as backslash (\), its throwing error.

SELECT * FROM tri_test('cde\fg');

Error:

Execution finished with errors.
Result: fts5: syntax error near "\"
At line 74:
SELECT * FROM tri_test('cde\fg');

The same issue is there when I try with MATCH operator as well

SELECT * FROM tri_test where tri_test MATCH 'cde\fg';

Is there a way to escape such character? Please help!

Answer 1

We can escape such special characters by adding DOUBLE quotes around the string you want to search.

SELECT * FROM tri_test where tri_test MATCH '"cde\fg"';