In a SQLITE3 DB, I have a table A with a column 'word' containing search words that partially match a column in another table B.

I'd like to SELECT all rows FROM table B WHERE B.name LIKE %any_of_the_words_in_a.word%';

My first idea was to use a REGEX, but I can't get it to work with REGEX and a subquery.

  • 1
    SELECT b.* FROM tablename AS b WHERE EXISTS (SELECT * FROM words AS w WHERE b.name LIKE CONCAT('%',wb.word,'%')); Dec 30 '15 at 12:03

I think this is what you are after, no need for Regex. It's just a correlated subquery, with the only difference being the LIKE condition instead of the common equality condition:

FROM tablename AS b 
      ( SELECT * 
        FROM words AS w 
        WHERE b.name LIKE '%' || w.word || '%'
      ) ;
  • They might need REGEX instead of LIKE to ensure that a word is used as a whole word and not as part of a word. But the query would essentially be the same as this, of course.
    – Andriy M
    Dec 30 '15 at 13:42
SELECT e.id, count(a.salary)
FROM (subquery here that returns "id"
     ) e left outer join
     TableA a
     on a.comment like '%'||e.id||'%'
group by e.id;
  • 2
    Welcome to the site. It would be helpful if you edited your answer to explain why you've adopted this solution. Dec 31 '15 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.