How do you match multiple column in a table with SQLite FTS3?

Question

Lets say I have a table with columns A , B , C , D and I want to do a match on column A and C

I see that you can either match a single column or all column in sqlite ie

- match column A
select * from table where A match 'cat' 

- match all columns
select * from table where table match 'cat'

Is it possible to match only columns A and C? If not, how do you guys get around this?

Thanks for your suggestion!

score 36 · Accepted Answer · answered Jan 29 '13 at 22:34

36

I don't think you can use multiple MATCH operators in a single FTS query. Use column_name:target_term to search multiple columns using one full text search.

SELECT * FROM table WHERE table MATCH 'A:cat OR C:cat'

http://www.sqlite.org/fts3.html#termprefix

answered Jan 29 '13 at 22:34

Rob Seed

464
5
4

what if cat contains `OR`? how do u escape it? – Alex Aug 26 '13 at 17:29
@Alex FTS searches whole terms, not substrings, so there is no need to escape something like 'A:poor OR C:or'. AND OR NOT must be uppercase, terms are lowercase. http://www.sqlite.org/fts3.html#section_3_1 – Rob Seed Aug 29 '13 at 22:19
For some weird reason: SELECT * FROM table WHERE table MATCH "field:v.sport 1" is giving me different results from SELECT * FROM table WHERE field MATCH "v.sport 1", given "V.sport 1" is my search term. – kroe Oct 14 '15 at 19:37
turns out when doing multiple field search the "." and the " " ( space ) where being considering different tokens, i had to tokenize then as described here: http://stackoverflow.com/questions/16080307/how-to-stop-being-treated-as-a-separator-in-sqlite-fts4 . Funny thing about this not hapenning when matching only the field explicitly on the query. – kroe Oct 14 '15 at 20:59

score 6 · Answer 2 · answered Aug 09 '11 at 20:56

Instead of specifying which columns to match to, you can specify the entire table to match to. For example:

-- Example schema
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

-- Example table population
INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');

-- Example queries
SELECT * FROM mail WHERE subject MATCH 'software';    -- Selects rows 1 and 2
SELECT * FROM mail WHERE body    MATCH 'feedback';    -- Selects row 2
SELECT * FROM mail WHERE mail    MATCH 'software';    -- Selects rows 1, 2 and 3
SELECT * FROM mail WHERE mail    MATCH 'slow';        -- Selects rows 1 and 3

what about when MATCH expression is unknown, so with java pst.setString(), how would the statement change? — Hoody, Jan 12 '13 at 11:38
Not a single of the examples that you just copied from docs works like you claim they are. Column is always specified in each. — DoTheEvo, Jun 14 '15 at 12:30

score 5 · Answer 3 · answered Sep 06 '16 at 13:35

For the multiple column condition using MATCH use UNION for the 'OR' and use INTERSECT for the 'AND'

SELECT * FROM TBL_VIRTUAL_APPOINTMENT WHERE PATIENT_PREFIX MATCH 'D*' 
UNION 
SELECT * FROM TBL_VIRTUAL_APPOINTMENT WHERE patient_first_name MATCH 'K*'

score 2 · Answer 4 · answered Aug 27 '13 at 15:38

Using sub-queries as workaround:

OR equivalent query, like a MATCH 'cat' OR c MATCH 'cat':

SELECT * FROM table
WHERE ROWID IN (
    SELECT ROWID FROM table WHERE a MATCH 'cat'
UNION
    SELECT ROWID FROM table WHERE b MATCH 'cat'
);

AND equivalent query, like a MATCH 'cat' AND c MATCH 'cat':

SELECT * FROM table WHERE ROWID IN (
    SELECT ROWID FROM table WHERE a MATCH 'cat'
    AND ROWID IN (
        SELECT ROWID FROM table WHERE b MATCH 'cat'
    )
);

score -1 · Answer 5 · answered Oct 30 '10 at 09:06

-1

You can concatenate columns using the || operator:

SELECT * FROM table WHERE a || b MATCH 'cat'

I'm not sure about the MATCH function in SQLite though, I think it will usually throw an exception (see http://www.sqlite.org/lang_expr.html#match).

answered Oct 30 '10 at 09:06

faxi05

325
2
5

2

No, sqlite doesnt seem to like that, although you can return concatenate column ie select a || b from table where a match 'cat' – Unikorn Oct 30 '10 at 12:57
What's the operator precedence here? Match or ||? Try some parentheses. – MPelletier Oct 30 '10 at 16:39
1

actually, I noticed that you can do something like this: select * from table where table match 'colA:cat OR colB:cat' , but the performance is horrible as compare to having colA and colB in one column and do a match against it. – Unikorn Oct 30 '10 at 18:04

How do you match multiple column in a table with SQLite FTS3?

5 Answers5

Linked