Search code examples
selectsearchsqlitesql-like

How do I find a word that certain rows contain in SQLite?

I am having a hard time figuring out how to do the following in SQLite:

I have a table with let's say the following:

table name: terms

  • golden
  • waterfall
  • inception
  • castaway

I would like to now do a lookup on all of the terms in the table that is contained in a specific string. So a string like "[email protected]" should return a match. Or "life_waterfall_5" should return a match.

I understand how to do this with the LIKE statement if it was the other way around (if I was looking for matches in the table that contains a specific word. But how do I do it in my case where I have to match all entries that is contained WITHIN my search term?

Solution

  • To find rows that contain a string:

    SELECT * FROM tbl WHERE col LIKE '%word%';

    To find rows that a string contains, just turn it backwards:

    SELECT * FROM tbl WHERE 'some string' LIKE '%' || col || '%';