SQLite Full Text Search Queries With Hyphens

full-text-search, sql, sqlite

Solution

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"';

Problem

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.

Original source