Geek asks and answers

Full SQLite Text Queries with Hyphens

I am using SQLite (3) for a small website. I recently discovered SQLite Full Text Search (FTS) and used it for a simple search function. However, the user inadvertently found that a hyphen ('-') in the search bar does the wrong thing. This seems to indicate that the token following it should be excluded. In fact, when I change the hyphen to plus or space, it works.

My questions are: 1) Am I correct in this analysis? I read the SQLite documentation regarding the FTS function and did not discuss it. 2) How do I mitigate this? Manually replace hyphens before passing them to SQLite?

A small, concrete example of what I see:

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)

( , .)

, - SELECT MATCH. "f-zero" SQL :

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., "F-Zero". "f + zero" :

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

, , "+" "-" SQLite, .

+3
1

:

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

, , :

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

, :

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

, :

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

More articles:


All Articles