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:

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

-- 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.

-- 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?

-- 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.

-- 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.

-- 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

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

SELECT * FROM users
WHERE email LIKE '%@gmail.com';

It’s useful when filtering contacts by email provider.

Use Single-Character Wildcards

-- 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:

-- 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.

-- 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:

-- 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.

-- 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.

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:

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.

-- 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.