SQLite Virtual Table Match Escape character

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.

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!

This question is older and involves fts3, but I thought I would add an update to show how you can do this using the newer fts5.

Let's start by setting up a test environment on the command line:

$ sqlite3 ":memory:"

Then creating an fts5 table that can handle the dash:

sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS blah USING fts5(term, tokenize="unicode61 tokenchars '-'");

Notice the subtle use of double and single quotes in the tokenize value.

With setup out of the way, let's add some values to search for:

sqlite> INSERT INTO blah (term) VALUES ('bacon-and-eggs');
sqlite> INSERT INTO blah (term) VALUES ('bacon');
sqlite> INSERT INTO blah (term) VALUES ('eggs');

Then let's actually search for them:

sqlite> SELECT * from blah WHERE term MATCH '"bacon-and-eggs"';
bacon-and-eggs
sqlite> SELECT * from blah WHERE term MATCH '"bacon"*';
bacon-and-eggs
bacon

Once again, notice the subtle use of double and single quotes for the search term.

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.