SQLite Full Text Search Queries With Hyphens

Question

I have been using SQLite (3) for a small website. Recently, I have discovered SQLite's full text search (FTS) capability and leveraged it for a simple search feature. However, a user inadvertently discovered that a hyphen ('-') in the search string does the wrong thing. It seems to indicate that the token immediately following should be excluded. In fact, when I change the hyphen to a plus or a space, it does work.

My questions: 1) Am I correct in this analysis? I read the SQLite documentation regarding the FTS feature and found no discussion about this. 2) How should I mitigate this? Manually replace hyphens before passing them to SQLite?

A small, concrete example of what I'm seeing:

sqlite> CREATE VIRTUAL TABLE fts_table USING fts4
   ...> ( content TEXT );

sqlite> INSERT INTO fts_table VALUES ("Title: F-1 Race (Game Boy)");
sqlite> INSERT INTO fts_table VALUES ("Title: F-Zero (SNES)");
sqlite> INSERT INTO fts_table VALUES ("Title: F-15 Strike Eagle II (Genesis)");

sqlite> SELECT * FROM fts_table;
Title: F-1 Race (Game Boy)
Title: F-Zero (SNES)
Title: F-15 Strike Eagle II (Genesis)

(This database is related to old video games, as you might have guessed.)

So the website takes the search string from the user and plugs it into a SELECT statement using the MATCH operator. For the search string 'f-zero', the relevant SQL becomes:

sqlite> SELECT * FROM fts_table WHERE content MATCH 'f-zero';
Title: F-1 Race (Game Boy)
Title: F-15 Strike Eagle II (Genesis)

I.e., it doesn't match the title 'F-Zero'. However, the string 'f+zero' returns the right thing:

sqlite> SELECT * FROM fts_table WHERE content MATCH 'f+zero';
Title: F-Zero (SNES)

Again, I suppose I could substitute '+' or a space for '-' before sending the string to SQLite, but that doesn't really feel like the right solution.

Answer 1

I found in the doc :

The NOT operator (or, if using the standard syntax, a unary "-" operator)

So they are the same, and the example :

-- Query for all documents that contain the term "database", but do not contain
-- the term "sqlite". Document 1 is the only document that matches this criteria.
SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';

That's the same as :

SELECT * FROM docs WHERE docs MATCH 'database -sqlite';

In your case your must use the Phrase queries, like this :

SELECT * FROM fts_table WHERE content MATCH '"f-zero"';