SQLite Virtual Table Match Escape character

Question

I'm working on an applications where the indices are stored in a SQLite FTS3 virtual table. We are implementing full text matches which means we send through queries like:

select * from blah where term match '<insert term here>'

That's all well and good until the term we want to match contains a hyphen in case the SQLite virtual match syntax interprets bacon-and-eggs as bacon, not and, not eggs.

Does anyone know of an escape character to make the fts table ignore the hyphen? I tried adding an ESCAPE '\' clause and using \ before each hyphen but the match statement rejects that syntax.

Thanks.

Answer 1

There are lots of strings that FTS considers "special" and that needs to be escaped. The easiest way to do that is to add DOUBLE quotes around the string you want to search for.

Example 1: Say the term you want to search for is bacon-and-eggs.

select * from blah where term match '"bacon-and-eggs"'

This also treats the whole string as a phrase, so hits with the same words in a different order doesn't generate any hits. To get around that you can quote each word separately.

Example 2: Say the term you want to search for is bacon and eggs.

select * from blah where term match '"bacon" "and" "eggs"'

Hope this helps someone!