I am trying to use FTS3 in sqlite and executing a query which is not returning desired results. here is the query:
select * from table1 where col1 MATCH 'rain';
this query is returning col1 containing text 'strain' too which I do not want. I want the exact replica of this query:
select * from table1 where col1 like '% rain %';
any suggestions/comments/help?
In case of FTS3/FTS4 in sqlite database, I conclude that the default tokenizer which is simple (at least) ignores '(' and ')' while tokenizing the elements in any text column. Other special characters can also be checked as I doubt there would be others too. When the match query is executed, these values were ignored. Here is the test case for my scenario:
create virtual table tab1 using fts3(val text);
insert into tab1 values('this is rain test');
insert into tab1 values('this is strain test');
insert into tab1 values('this is (rain) test with parenthesis');
insert into tab1 values('rain test');
insert into tab1 values('this is rain');
insert into tab1 values('strain test');
insert into tab1 values('this is strain');
after executing the following query:
select * from tab1 where val MATCH 'rain';
results were:
this is rain test
this is (rain) test with parenthesis
rain test
this is rain