One week. 700+ courses for $0. No credit card needed.
Start Now
Suppose you have an employees table and would like to find all names that start with ‘A’:
employees | |||||
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02T00:00:00.000Z | Georgi | Facello | M | 1986-06-26T00:00:00.000Z |
10002 | 1964-06-02T00:00:00.000Z | Bezalel | Simmel | F | 1985-11-21T00:00:00.000Z |
10003 | 1959-12-03T00:00:00.000Z | Parto | Bamford | M | 1986-08-28T00:00:00.000Z |
You could spend time looking through the table manually. But why would you do that when you have the LIKE operator?
The magic here is in the clause `WHERE first_name LIKE ‘A%’`, which means “find all first_name starting with "A" and ending with any number of characters.” The A% here is known as a pattern for matching.
The % is not the only wildcardyou can use in conjunction with the LIKE operator. You could usethe underscore sign _, too:
`%` matches any number of characters._ matches any single character.The syntax is easy to remember. It is simply:
You can use LIKE to achieve a variety of pattern-matching. Here’s how:
Below, we’ve outlined some practical examples of how you can use the LIKE statement and the results from our sample data set.
If you’d like to perform an exact string match, use LIKE without ‘%’ or ‘_’
SELECT
first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’‘%’ can be used to match any (even zero) number of characters – a number, an alphabet, or a symbol.
Suppose you want to find all employees whose name starts with ‘Adam’; you can use the pattern ‘Adam%’
To find names that end with "z" try the pattern ‘%z’. You can also use multiple ‘%’ in one pattern. For example, if you want to find names that contain "z", use ‘%z%’.
Like the game Hangman, the underscore sign _ can only fit one character.
How many ways are there to spell Le_n? The pattern would match anything from "Lexn", "LeAn", "Le3n", or "Le-n".
What are the different names with only three characters? We can find out using three consecutive underscores ___ as the pattern.
Of course, you can use both ‘%’ and ‘_’ to create interesting patterns.
The pattern ‘%ann_’ matches a string that starts with any number of characters and ends with "ann" and one other character.
What if you want to find all rows that do not match a specific criterion? You can use the NOT LIKE operator. For example, to find all titles except for "Staff", we can use the syntax
`WHERE title NOT LIKE ‘Staff’`This is exactly equivalent to the syntax
WHERE title != 'Staff'Of course, you can use NOT LIKE with any of the patterns we described.
You can use the following syntax if you need to perform pattern matching but aren’t sure if the string is stored in lowercase, uppercase, or mixed case.
LOWER(column_name) LIKE patternThe function LOWER() returns all strings in lowercase, regardless of whether they are stored as upper-, lower-, or mixed case.
When using the syntax, make sure you spell the pattern in all lowercase! Else, you might not get any matches.
You could replace LOWER with UPPER in the syntax above, too. Be sure to spell out the pattern in capital letters.
For example, to find out if an employee’s name is "Joanne", "JoAnne", "Joanna", or "JoAnna", try either of the following:
SELECT DISTINCT
first_name
FROM employees
WHERE LOWER(first_name) LIKE 'joann_'You can combine multiple conditions using the LIKE syntax, too.
For example, use the OR condition to find results that satisfy at least one out of multiple LIKE patterns.
On the other hand, to find a string that matches multiple LIKE conditions, use the AND keyword.
The LIKE syntax can be applied to multiple columns as long as their variable type is a variable-length character (varchar). Knowing that we can find the names of employees whose initials are "Z. Z."
SELECT DISTINCT
first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'Thus far, we have focused on using LIKE as acondition for selecting records in the WHERE clause. We also useLIKE in the SELECT clause. For example, can we find out how many employees named "Adam" are in our database?
On the other hand, how many employees have the initials "A.Z."?
SELECT
COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employeesHere's a summary of the patterns we discussed for quick reference:
| Pattern | Description | Example use case |
|---|---|---|
| A% | Matches strings starting with "A" | Finding names starting with "A" |
| %z% | Matches strings containing "z" | Finding names containing "z" |
| Le_n | Matches strings like "Len", "Leon", etc. | Finding names with a single character variation |
| %ann_ | Matches strings ending with "ann" and one additional character | Finding names like "Joann", "Joanna" |
The LIKE function is largely similar across different flavors of SQL (e.g. PostgreSQL, MySQL, Redshift, etc.). However some have additional variations of the LIKE function that are worth mentioning.
Available in Redshift and PostgreSQL, ILIKEis the case-insensitive version of LIKE. As such, all of the following are equivalent.
SELECT
datacamp ILIKE ‘datacamp’, -- returns TRUE
DATACAMP ILIKE ‘datacamp’, -- returns TRUE
Datacamp ILIKE ‘datacamp’, -- returns TRUE
datacamp ILIKE ‘DataCamp’, -- returns TRUEUsers of T-SQL or SQL Server have additional wildcard characters for more complex pattern matching.
The square bracket syntax [] matches any specific character within the range or set. For example, the following will all return TRUE.
SELECT
‘Carson’ LIKE ‘[C-K]arson’, -- returns TRUE because C is in the range C-K
‘Karson’ LIKE ‘[C-K]arson’, -- returns TRUE because K is in range
‘Larson’ LIKE ‘[CKL]arson’, -- returns TRUE because L is in the set [CKL]
‘Parson’ LIKE ‘[C-K]arson’ -- returns FALSE because P is out of rangeThe caret-in-square-bracket [^] wildcard matches any single character that is not within the specified range or set. Can you see why the following results are such?
SELECT
‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE
‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUEHere, since "C" is within the range of [C-K], the pattern ‘C’ will not match [^C-K]. Thus, "Carson" will not match [^C-K]arson, but "Parson" will.
Available in MySQL, the RLIKE operator recognizes regular expressions (RegEx) in the pattern. RegEx is a powerful and versatile tool for advanced pattern matching.
It does not hurt to have a basic understanding of RegEx, especially if your SQL flavor supports RLIKE. You can learn more about RegEx with our Regular Expressions in Python course.
In PostgreSQL, ‘~~’ is completely synonymous with LIKE. There are also equivalents of ILIKE, NOT LIKE, and NOT ILIKE.
Operator | Equivalent |
|
|
|
|
|
|
|
|
Here are some common errors you may run into when using LIKE, and how to correct them:
LIKE is case-sensitive by default. Use LOWER() or ILIKE (if supported) for case-insensitive matching.% and _ are used correctly. % matches any number of characters, while _ matches exactly one character. Misplacing these can lead to unexpected matches.% in patterns (e.g., %pattern), which disables index usage. Rewrite patterns to start with a specific string if possible. See the section below for more on performance. % and _ in the search string must be escaped if intended as literal characters. Use ESCAPE in your query to define an escape character.WHERE column_name LIKE '50\% OFF' ESCAPE '\'The LIKE operator is great, but it can potentially impact query performance, especially when used on large datasets. Here are some considerations to optimize its use:
LIKE operator performs best when the pattern starts with a constant string, such as Adam%, because the database can use indexes. However, patterns like %Adam or %Adam% require a full table scan, which can be slow for large tables.%, such as %pattern, disables index usage, as the database has to examine every record.LOWER() or UPPER() on columns for case-insensitive searches can also prevent indexes from being used. Instead, make sure the database collation is set appropriately for case-insensitive comparisons.GIN indexes in PostgreSQL or FULLTEXT indexes in MySQL, when performing complex or frequent string matching.LIKE operator.Full-text search is available in databases like MySQL, PostgreSQL, and SQL Server. While LIKE works well for straightforward pattern matching, full-text search is built for more advanced text queries. Here is how the two approaches compare:
| Feature | LIKE | Full-Text Search |
|---|---|---|
| Syntax | WHERE col LIKE '%term%' | MATCH(col) AGAINST('term') |
| Wildcards | %, _ | Boolean operators, natural language |
| Index support | Only prefix patterns (e.g., term%) | Dedicated full-text indexes (GIN, FULLTEXT) |
| Performance on large tables | Slow with leading % | Optimized for large text columns |
| Relevance ranking | No | Yes |
| Linguistic features | No | Stemming, stop words, synonyms |
| Best for | Exact patterns, small to medium datasets | Keyword search, large text columns |
For more advanced string operations beyond pattern matching, see the SQL CONTAINS tutorial.
Mastering SQL functions is key to succeeding in data science, and SQL’s LIKE command is no exception. A good command of SQL will supercharge your analytics progress, so be sure to learn it well!
For more resources on SQL, be sure to check out the following:
No, the LIKE operator is specifically used for pattern matching with string data types such as CHAR, VARCHAR, and TEXT. For numeric data types, other comparison operators such as =, <, >, etc., are used.
The LIKE operator can be less efficient, especially with patterns starting with %, as it requires a full table scan. Indexing can't be used effectively in these cases, which might slow down queries on large datasets.
While LIKE itself is not inherently insecure, using user inputs directly in SQL queries can lead to SQL injection attacks. Always sanitize inputs and consider using parameterized queries to mitigate this risk.
In SQL flavors without ILIKE, you can use LOWER(column_name) LIKE LOWER(pattern) or UPPER(column_name) LIKE UPPER(pattern) to perform case-insensitive searches.
Yes, LIKE can be used with non-ASCII characters as long as the database encoding supports those characters. This includes UTF-8, which is commonly used to support a wide range of characters.
To search for a literal % or _, you need to use an escape character. For example, in SQL Server, you might use LIKE 'A[%]%' ESCAPE '%' to search for strings containing a literal %.
Yes, combining LIKE with functions like CONCAT or SUBSTRING can help refine search patterns. For example, using CONCAT can dynamically construct patterns based on other column values.
LIKE can be applied in JOIN conditions to match patterns between columns of different tables. For instance, ON table1.col1 LIKE table2.col2 || '%' can be used to join tables where table1.col1 starts with table2.col2.
For more complex patterns, SQL flavors that support regular expressions, such as MySQL's RLIKE or PostgreSQL's SIMILAR TO, can be used. These provide a richer syntax for advanced pattern matching.
When a column contains null values, LIKE will not match these records, as nulls are not considered equal to any string or pattern. To include nulls, use a condition like OR column IS NULL.

บทแนะนำ
บทแนะนำ
บทแนะนำ
Learn more about SQL
คอร์ส
เชี่ยวชาญการเขียนคำสั่ง SQL ที่ซับซ้อนเพื่อตอบคำถามด้าน data science หลากหลายและเตรียมชุดข้อมูลที่แข็งแกร่งสำหรับการวิเคราะห์ใน PostgreSQL
Like it or not, the
LIKEoperator is essential in SQL. It gives data scientists and data engineersthe power to filter data on specific string matches.In this tutorial, I’ll walk you through how to useLIKEfor pattern matching, from the basics to more advanced techniques.TL;DR
LIKEoperator uses two wildcards for pattern matching:%(any number of characters) and_(exactly one character)LIKEwithNOT,LOWER()/UPPER(),OR/AND, andCASE WHENfor flexible filteringILIKE(PostgreSQL/Redshift) for case-insensitive matching, or wrap columns inLOWER()%wildcards (e.g.,%pattern) disable index usage—consider full-text search for large datasets[]and[^]for character ranges; MySQL supportsRLIKEfor regex-based matchingThe SQL LIKE Operator for Pattern Matching
Note: To run all the example code in this tutorial yourself, you can create a free DataLab workbook with SQL installed and databases with sample data.