0

I am learning how to use FTS4 in SQlite and trying to translate something like this:

SELECT * FROM table 
WHERE (postal_code LIKE q1 OR place_name LIKE q1) AND (address LIKE q2)

which works perfectly with the normal table, to something like that:

SELECT * FROM table
WHERE table MATCH '(postal_code: q1 OR place_name: q1) AND (address: q2)'

which is not working with the same table converted to VIRTUAL .. USING fts4...

Searching for match in specific column with ":" is working, but how can I combine this with OR and AND?

Background - I try to work with a flexible query, where q1 can be one of the both: postal-code or place name and q2 has to be address.

EDIT:

This is what I got so far (partially) working

SELECT * FROM table WHERE table MATCH ('postal_code:q1 OR place_name:q1')
INTERSECT
SELECT * FROM table WHERE table MATCH ('address:q2');

(building on this post from @CL)

The first problem here are words with space - if I search for something like

...MATCH ('postal_code:"new haven*" OR place_name:"new_haven*"')

then I have "malformed MATCH expression" The second problem is when I have empty query, like

...MATCH ('*')

then I get nothing

Community
  • 1
  • 1
Vladimir
  • 1,045
  • 1
  • 11
  • 23
  • Is your FTS [compiled with the enhanced query syntax](http://www.sqlite.org/fts3.html#compiling_and_enabling_fts3_and_fts4)? – CL. May 06 '17 at 14:46
  • I suppose yes, because [this](http://stackoverflow.com/a/26021690/6049386) is working, anyway only with a single OR. – Vladimir May 07 '17 at 06:10

0 Answers0