What is the LIKE statement in SQL?
The SQL LIKE operator is used to search for a specific pattern in a string value. It is often used in the WHERE clause of a SELECT, UPDATE, or DELETE statement.
You can use two wildcard characters to help you define what you are looking for in a database. The percent sign (%) matches any number of characters, and the underscore (_) corresponds to a single character.
The following SQL statement exemplifies how the LIKE operator returns the rows from the tv_show table where the GENRE column starts with “Thriller”.
SELECT *FROM SANDBOX.tv_showWHERE GENRE LIKE 'Thriller%';To understand the difference, compare the result when we used “=” instead of LIKE.
SQL LIKE allows you to search for data that matches a specific pattern. It is handy when you only know partially the records you are searching because it helps you perform a flexible search that considers variations in the data.
Case sensitivity varies depending on the specific database platform and configuration. (Consult the documentation for case sensitivity behavior within your specific database platform. See notes below for LIKE case sensitivity within RedShift, BigQuery, and Snowflake.)
Searching for a specific string:
SELECT *FROM SANDBOX.tv_showWHERE SHOW_NAME LIKE 'The Office';Note that without the ‘%’ wildcard character, LIKE is equivalent to using the equals operator.
Searching for a pattern:
SELECT *FROM SANDBOX.tv_showWHERE SHOW_NAME LIKE 'The%';Searching for records that contain a specific word pattern:
SELECT *FROM SANDBOX.tv_showWHERE SHOW_NAME LIKE '%Twil%';Searching for records that match multiple conditions:
SELECT *FROM SANDBOX.tv_show WHERE SHOW_NAME LIKE 'Th%' AND GENRE LIKE 'Doc%';Note that this one helps you find results with typos. You can combine it with other operators and wildcard characters to perform more complex searches and find patterns in your data.
Case Sensitivity
The case sensitivity of the LIKE operator can vary depending on the specific database platform and its configuration. Here’s an overview of the behavior of the LIKE operator in Redshift, BigQuery, and Snowflake:
- Redshift: By default, the LIKE operator is case-sensitive in Redshift. However, you can use the ILIKE operator instead of LIKE to perform a case-insensitive search.
- BigQuery: The LIKE operator is case-sensitive in BigQuery. However, you can use the REGEXP_CONTAINS function to perform a case-insensitive search using regular expressions.
- Snowflake: The behavior of the LIKE operator in Snowflake is determined by the case sensitivity of the collation of the column being searched. By default, the collation is case-insensitive, so the LIKE operator will perform a case-insensitive search. However, if the column has a case-sensitive collation, the LIKE operator will be case-sensitive. You can also use the ILIKE operator to explicitly perform a case-insensitive search, regardless of the column’s collation.
It’s always a good idea to consult the documentation for your specific database platform to understand its behavior regarding case sensitivity and the LIKE operator.
