CODEWITHSUNDEEP
sqlsqlitefull-text-search
pletoss
pletoss

Reputation: 323

FTS sqlite phrase search for single column not possible

i've been using the FTS extension of sqlite, and can't seem to figure out how to do a phrase search for one column while matching some other text for some other. Usually doing a phrase search works for ALL columns like:

SELECT * from ftstable where ftstable MATCH '"Phrase With Spaces In It"';

and for a specific column with:

SELECT * from ftstable where body MATCH '"Phrase With Spaces In It"';

but doesn't work for a aggregated search in two columns like:

SELECT * from ftstable where ftstable MATCH 'body:"Phrase With Spaces In It" title:"The*"';

and instead throws:

Error: malformed MATCH expression: [body:"Phrase With Spaces In It" title:"The*"]

I found this thread claiming it is not possible (November 2011) : http://osdir.com/ml/sqlite-users/2011-11/msg00363.html . Does anyone know if this is possible now ?

I can only imagine a workaround using an 'INTERSECT' but the problem is then the resulting two sets that get intersected are too big and the query will be much slower and memory inefficient, especially since i deploy this on a mobile device.

Thanks.

Upvotes: 4

Views: 1404

Answers (3)

flauschtrud
flauschtrud

Reputation: 908

I stumbled upon the same problem with FTS4 and thanks to Rinzwind's comment stopped to try and find some solution using quotes. It's really just weird.

Robert Hawkey's idea did not work for me either since it produces an exception: SQLiteQuery: exception: unable to use function MATCH in the requested context. Apparently it is not possible to have multiple MATCH in a WHERE clause.

I finally solved my problem using a subselect. For the example of this question something like

SELECT * from ftstable WHERE body MATCH '"Phrase With Spaces In It"' AND rowid in (SELECT rowid from ftstable WHERE title MATCH '"The*"');

Upvotes: 0

Rinzwind
Rinzwind

Reputation: 31

Any filter with a column specifier can't contain spaces. It just does not work. It's weird but true. col1:abc works, col1:"abc" does not. col1: "abc 123" does not (it just matches any column with "abc 123" int it).

Upvotes: 2

Robert Hawkey
Robert Hawkey

Reputation: 771

Have you tried:

SELECT * FROM ftstable WHERE (ftstable MATCH 'body:"phrase with spaces in it"') AND (ftstable MATCH 'title:The*')

Upvotes: 3

Related Questions