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 you only want strings that end with the substring str, you can use %str.

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

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

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

If you want to match fields that contain any of the possible substrings, you 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 you need all substrings to be present, you can use AND instead of OR.

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