3

I am having some problems with special characters in my scenario. I have a sqlite db created using fts3.

When I use SELECT col_1, col_2, offsets(table) FROM table WHERE table MATCH 'h*' LIMIT 50;

I am able to get words which start with h.

but when I am using

SELECT col_1, col_2, offsets(table) FROM table WHERE table MATCH '@*' LIMIT 50;

I am not getting strings which start with @.

Where am I going wrong? Any pointer regarding approach would be great.

mmmmmm
  • 32,227
  • 27
  • 88
  • 117
Arup
  • 384
  • 1
  • 10

1 Answers1

0

I think the behavior you described happens because SQLite FTS3 uses tokenizer called "simple" by default. The character @ gets discarded because is not an alphanumeric character and its UTF codepoint is not greater than 127. My interpretation of this is that FTS is not for searching special characters, it is for searching natural text.

The fix I suggest is not to use FTS for this kind of queries but to use LIKE operator. Or you could try to search for other tokenizers available or write your on in C.

XDF
  • 147
  • 1
  • 7