Multiple LIKE in sqlite

If you have SQLite FTS3 and FTS4 Extensions enabled then you can take advantage of Full Text Search (FTS) capabilities. You will need to recreate the p_extra_fields table as a VIRTUAL table. Then you can insert OR between your search terms and use the MATCH operator...

SELECT p.* FROM p_extra_fields xJOIN products p ON p.id = x.productWHERE x.key IN ('model', 'color', 'make')AND x.type = '1'AND x.value MATCH 'foo OR bar'GROUP BY x.product LIMIT 0, 50;

Good info here also.Click here to see it in action at SQL Fiddle.

I think this where clause is simpler:

  WHERE x.type = "1" and        x.key in ('model', 'color', 'make') and        (x.value like '%foo%' or x.value like '%bar%')

I had same requirement and I was looking for a mechanism that would a match like REGEXP "A|B|C" which means match either A, B, C.

So finally this is the solution I came up with:

WITH words(str, strSubString, hasComma) AS (  VALUES ('', "foo,bar", 1)  UNION ALL SELECT              SUBSTR(strSubString, 0,                     CASE WHEN INSTR(strSubString, ',')                       THEN INSTR(strSubString, ',')                     ELSE LENGTH(strSubString) + 1 END),              LTRIM(SUBSTR(strSubString, INSTR(strSubString, ',')), ','),    INSTR(strSubString, ',')      FROM ssgPaths      WHERE hasComma  )SELECT p.* FROM p_extra_fields as x INNER JOIN products as p ON x.product = p.idJOIN words AS w ON x.value LIKE '%' || w.str || '%' AND w.str != ''WHERE x.type = "1" and x.key in ('model', 'color', 'make');

The matching criteria is equivalent to @Gordon's answer:

WHERE x.type = "1" and        x.key in ('model', 'color', 'make') and        (x.value like '%foo%' or x.value like '%bar%')

But this gives you the flexibility of matching the values dynamically depending on your query parameter ( you can extract out "foo,bar" to be a parameter).

E.g without changing your query you can pass "foo,bar,boo" and till match similar to regex match: "foo|bar|boo"