How do I escape punctuation in full text queries

(1) By Jaseem Abid (jabid1) on 2022-05-03 23:13:09 [link] [source]

Hey folks! I have a fairly simple virtual table with (id, text) schema which I use for full text search with fts5 module.

How do I search with special characters/punctuation like @, # and ? characters?

All of the following leads to syntax errors.


sqlite> SELECT id from fts where text MATCH "@foo" order by rank;
Error: stepping, fts5: syntax error near "@" (1)

sqlite> SELECT id from fts where text MATCH ""@foo"" order by rank;
Error: in prepare, near "@foo": syntax error (1)

sqlite> SELECT id from fts where text MATCH "\"@foo\"" order by rank;
Error: in prepare, near "@foo": syntax error (1)

sqlite> SELECT id from fts where text MATCH "\@foo" order by rank;
Error: stepping, fts5: syntax error near "\" (1)

(2) By Larry Brasfield (larrybr) on 2022-05-03 23:33:32 in reply to 1 [source]

I would start by properly quoting your string literals. For example contrast:

   SELECT 'This is a string literal';
 with
   SELECT * FROM "Oddly named table";

(3) By Jaseem Abid (jabid1) on 2022-05-03 23:38:04 in reply to 2 [link] [source]

Not really sure what you mean here, since this also leads to the same error.

sqlite> SELECT * from fts where text match '@cat';
Error: stepping, fts5: syntax error near "@" (1)

(4) By Jaseem Abid (jabid1) on 2022-05-03 23:47:23 in reply to 2 [link] [source]

Ok! Thanks @larrybr. You were right.

SELECT * from fts where text match '"@foo"';

worked fine. I really need to relearn sql.

(5) By anonymous on 2023-04-06 06:02:04 in reply to 4 [link] [source]

What if your string contains single quotes 'hello it's me' ?

(6) By Larry Brasfield (larrybr) on 2023-04-06 15:36:15 in reply to 5 [link] [source]

One option is explained here.

(7) By Lawrence D'Oliveiro (ldo289) on 2023-04-08 21:25:12 in reply to 5 [link] [source]

In standard SQL syntax, “'” characters are represented literally by writing them twice: “''”.