How to Match Columns that Contain a Substring in SQL

Suppose we’re querying a dataset from a SQL table.

Let’s say we want to select all rows where a column contains the substring str. We can use a percent sign % with LIKE to check for substrings in any column.

SELECT * FROM table_name
WHERE col LIKE '%str%' -- Can match "__str__"

If we only want strings that end with the substring str, we can use %str.

SELECT * FROM table_name
WHERE col LIKE '%str' -- Can match "__str"

If we only want strings that start with the substring str, we can use str%.

SELECT * FROM table_name
WHERE col LIKE 'str%' -- Can match "str__"

If we want to match fields that contain any of the possible substrings, we can use multiple WHERE clauses with OR.

SELECT * FROM table_name
WHERE col LIKE '%str1%' -- Can match "__str1__"
   OR col LIKE '%str2%' --       and "__str2__"

If we need all substrings to be present, we can use AND instead of OR.

SELECT * FROM table_name
WHERE col LIKE '%str1%'
  AND col LIKE '%str2%' -- Can match "__str1__str2__"