SQL LIKE Operator: Syntax, Usage, and Examples
The SQL LIKE operator helps you search for patterns within text. Instead of checking for exact matches, you can use this operator to match parts of a string—like names that start with “A” or emails that end in “.com.”
When you need flexible filters in text columns, the SQL LIKE operator is your go-to tool.
How to Use the SQL LIKE Operator
The basic syntax for the SQL LIKE operator looks like this:
sql
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
The pattern includes wildcard characters:
%matches any number of characters (including zero)_matches exactly one character
Examples of Basic Patterns
sql
-- Names starting with A
SELECT * FROM users
WHERE name LIKE 'A%';
-- Emails ending in .com
SELECT * FROM users
WHERE email LIKE '%.com';
-- Usernames with exactly 5 characters
SELECT * FROM users
WHERE username LIKE '_____';
With the SQL LIKE operator, you can make your searches as broad or specific as you need.
When to Use the LIKE Operator in SQL
Find Values That Begin or End a Certain Way
You can filter for values that start or end with specific substrings.
sql
-- Products that start with 'Pro'
SELECT * FROM products
WHERE product_name LIKE 'Pro%';
Match Substrings Anywhere in the Text
Want to find records that contain a keyword?
sql
-- Posts that mention 'sale' anywhere in the title
SELECT * FROM blog_posts
WHERE title LIKE '%sale%';
This pulls in titles like “Big Summer Sale” and “Flashsale Alerts.”
Handle Uncertain or Incomplete Data
Sometimes you only know part of a value. LIKE gives you a way to search anyway.
sql
-- Customers whose last name starts with Mc
SELECT * FROM customers
WHERE last_name LIKE 'Mc%';
You don’t need the whole string—just a piece of it.
Use Patterns in Data Validation or QA
You might also use the SQL LIKE operator to check for bad data entries or inconsistencies.
sql
-- Find phone numbers that don’t follow a certain format
SELECT * FROM contacts
WHERE phone NOT LIKE '___-___-____';
This checks if all phone numbers follow the “123-456-7890” format.
Examples of the LIKE Operator in SQL
Find Records That Match a Prefix
sql
SELECT * FROM cities
WHERE name LIKE 'San%';
This query pulls in cities like San Diego, San Jose, and San Francisco.
Search for Emails with a Domain
sql
SELECT * FROM users
WHERE email LIKE '%@gmail.com';
It’s useful when filtering contacts by email provider.
Use Single-Character Wildcards
sql
-- Product codes like A1B, A2B, A3B
SELECT * FROM inventory
WHERE product_code LIKE 'A_B';
Here, _ matches just one character, so A1B and A2B would match, but AB wouldn’t.
SQL Query LIKE Operator with Numeric Fields
Some databases let you apply LIKE to numeric fields, but it’s usually better for text. If needed, convert the number to text first:
sql
-- Find order IDs starting with 2023
SELECT * FROM orders
WHERE CAST(order_id AS VARCHAR) LIKE '2023%';
You’ll want to be careful with performance here, though.
Learn More About LIKE in SQL
Case Sensitivity
In SQL Server, LIKE is case-insensitive by default. In PostgreSQL, it’s case-sensitive unless you use ILIKE.
sql
-- PostgreSQL (case-insensitive match)
SELECT * FROM users
WHERE name ILIKE 'john%';
Combine LIKE with NOT
If you want to exclude a pattern, use the NOT LIKE operator in SQL:
sql
-- Exclude users with test emails
SELECT * FROM users
WHERE email NOT LIKE '%@test.com';
Use this when cleaning up test accounts or spammy data.
Escape Special Characters
If your pattern includes % or _ as part of the actual text, you can escape them with a backslash or use the ESCAPE clause.
sql
-- Find values that literally contain "100%"
SELECT * FROM feedback
WHERE comment LIKE '100\%%' ESCAPE '\';
This prevents % from being interpreted as a wildcard.
Combine LIKE with OR
Use OR to match multiple patterns in a single query.
sql
SELECT * FROM files
WHERE filename LIKE '%.jpg'
OR filename LIKE '%.png'
OR filename LIKE '%.gif';
You get all images, regardless of extension.
Use LIKE in Subqueries
You can combine LIKE with subqueries to filter based on dynamic values:
sql
SELECT *
FROM customers
WHERE name LIKE (
SELECT preferred_prefix || '%'
FROM user_settings
WHERE user_id = 1
);
This lets you build smart filters that adapt based on user input or config settings.
Performance Considerations
LIKE can slow things down—especially with leading wildcards ('%term'). SQL engines can’t use indexes effectively in those cases. If performance matters, consider full-text search for complex pattern matching.
SQL LIKE Operator vs. IN
The SQL like operator uses patterns, while IN checks exact values.
sql
-- LIKE
SELECT * FROM logs WHERE message LIKE '%error%';
-- IN
SELECT * FROM logs WHERE status IN ('error', 'fail');
Use LIKE when you don’t know the exact value or need to search partial text.
The SQL like operator gives you the flexibility to search with patterns instead of exact values. Whether you’re filtering emails, cleaning up data, or building admin reports, this operator adds a lot of power to your SQL queries.
Combine it with NOT, OR, and ESCAPE for even more precision, and keep in mind the performance trade-offs when using wildcards. Once you get comfortable with it, LIKE becomes one of your most useful tools in writing readable, effective SQL.