SQLite Virtual Table Match Escape character

Question

Heya,

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

FTS ignores all non-alphanumeric characters in the index. Before sending the search term to FTS you can convert it to

bacon NEAR/0 AND NEAR/0 eggs

to search for adjacent words.