FTS3 and FTS4 matching of :, -, and _ characters

Question

I'm seeing some weird behaviour on my FTS enabled SQLite database. I have a table named `fingerprints` that contains a column named `scan`. Entries of scan are long strings that look like this: ``` 00:13:10:d5:69:88_-58;0c:85:25:68:b4:30_-75;0c:85:25:68:b4:34_-76;0c:85:25:68:b4:33_-76;0c:85:25:68:b4:31_-76;0c:85:25:68:b4:35_-76;00:23:eb:ad:f6:00_-87; etc ``` It represent MAC addresses and signal strengths. Now I want to do string matching on the table and try to match for instance a MAC address: ``` SELECT _id FROM fingerprints WHERE scan MATCH "00:13:10:d5:69:88"; ``` This returns a lot of rows that do not have the specified string in it for some reason. Second thing I will try to match is ``` SELECT _id FROM fingerprints WHERE scan MATCH "00:13:10:d5:69:88_-58"; ``` This returns the same rows has before and is completely wrong. Does SQLite treats the `: _ -` characters in any special way? Thanks

Accepted Answer

What you're seeing is the effect of the FTS tokenizing your data.

The full text search doesn't work on un-processed long strings, it splits your data (and your search terms) into words and indexes them individually. The default tokenizer uses all alphanumeric characters and all characters with a code point >128 for words, and uses the rest of the characters (for example, as you're seeing `: _ -`) as word boundaries.

In other words, your search for `00:13:10:d5:69:88` will search for rows containing the words `00` and `13` and `10` and `d5` and `69` and `88` in any order.

You can verify this behavior;

```
sqlite> CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple);
sqlite> INSERT INTO simple VALUES('00:13:10:d5:69:88');
sqlite> SELECT * FROM simple WHERE simple MATCH '69:10';

-> 00:13:10:d5:69:88
```

EDIT: Apparently SQLite is smarter than I originally gave it credit for, you can use phrase queries (scroll down about a page from the link destination) to look for word sequences, which would solve your problem. Phrase queries are specified by enclosing a space (or other word separator) separated sequence of terms in double quotes (").

```
sqlite> SELECT * FROM simple WHERE simple MATCH '"69:10"';

-> No match

sqlite> SELECT * FROM simple WHERE simple MATCH '"69 88"';

-> 00:13:10:d5:69:88

sqlite> SELECT * FROM simple WHERE simple MATCH '"69:88"';

-> 00:13:10:d5:69:88
```

FTS3 and FTS4 matching of :, -, and _ characters

Solution

Problem