SQL LIKE Operator and Pattern Matching: Complete Guide (2026)

The SQL LIKE operator lets you search for patterns in text columns instead of matching exact values. It is one of the most frequently used features in SQL, whether you are filtering email addresses, searching names, or validating data formats. This guide covers everything from basic wildcards to advanced regex matching across PostgreSQL, MySQL, and SQL Server.

What Is the SQL LIKE Operator?

The LIKE operator is used in a WHERE clause to filter rows based on a pattern rather than an exact match. It works with two wildcard characters: % (percent) and _ (underscore).

The basic syntax is straightforward:

SELECT column1, column2
FROM table_name
WHERE column1 LIKE 'pattern';

Without wildcards, LIKE behaves exactly like the = operator. Its power comes from combining it with wildcards to match partial strings, prefixes, suffixes, and character-level patterns.

Wildcards: % and _ with Examples

SQL provides two wildcard characters for use with LIKE. Each serves a different purpose.

The % Wildcard (Percent)

The % wildcard matches any sequence of zero or more characters. It is the most commonly used wildcard.

-- Names starting with 'J'
SELECT * FROM users WHERE first_name LIKE 'J%';
-- Matches: John, Jane, James, J

-- Names ending with 'son'
SELECT * FROM users WHERE last_name LIKE '%son';
-- Matches: Johnson, Wilson, Anderson

-- Names containing 'an' anywhere
SELECT * FROM users WHERE first_name LIKE '%an%';
-- Matches: Jane, Daniel, Brandon, Joanne

-- Email addresses from a specific domain
SELECT * FROM users WHERE email LIKE '%@company.com';
-- Matches: john@company.com, hr@company.com

The _ Wildcard (Underscore)

The _ wildcard matches exactly one character. Use it when you need precise control over the number of characters in a position.

-- Three-letter names starting with 'T'
SELECT * FROM users WHERE first_name LIKE 'T__';
-- Matches: Tom, Tim, Ted (exactly 3 characters)

-- Product codes with a specific format: 2 letters, dash, 4 digits
SELECT * FROM products WHERE sku LIKE '__-____';
-- Matches: AB-1234, XY-5678

-- Second character is 'a'
SELECT * FROM users WHERE first_name LIKE '_a%';
-- Matches: James, Daniel, Karen, Paul

Combining % and _

You can mix both wildcards in a single pattern for more targeted matching.

-- Names starting with any character, then 'oh', then anything
SELECT * FROM users WHERE first_name LIKE '_oh%';
-- Matches: John, Johanna, Bohdan

-- Five-character strings ending in 'er'
SELECT * FROM users WHERE last_name LIKE '___er';
-- Matches: Baker, Mayer, Rober (exactly 5 characters)

Escaping Wildcards

When you need to search for a literal % or _ character, use the ESCAPE clause.

-- Find values containing a literal '%'
SELECT * FROM discounts WHERE code LIKE '%\%%' ESCAPE '\';
-- Matches: '20%OFF', '50%SALE'

-- Find column values containing a literal underscore
SELECT * FROM files WHERE filename LIKE '%\_v2%' ESCAPE '\';
-- Matches: 'report_v2.pdf', 'data_v2_final.csv'

NOT LIKE

NOT LIKE returns rows that do not match the specified pattern. It is the inverse of LIKE.

-- Users whose email is NOT from Gmail
SELECT * FROM users WHERE email NOT LIKE '%@gmail.com';

-- Products that do not start with 'TEST'
SELECT * FROM products WHERE product_name NOT LIKE 'TEST%';

-- Exclude internal accounts
SELECT * FROM users
WHERE email NOT LIKE '%@internal.company.com'
  AND email NOT LIKE '%@test.%';

NOT LIKE is commonly used for data cleanup, filtering out test records, or excluding specific domains from query results.

LIKE vs ILIKE (Case Sensitivity)

Case sensitivity with LIKE varies by database. This is one of the most common sources of confusion.

PostgreSQL

In PostgreSQL, LIKE is case-sensitive by default. Use ILIKE for case-insensitive matching.

-- Case-sensitive: only matches lowercase 'john'
SELECT * FROM users WHERE first_name LIKE 'john%';

-- Case-insensitive: matches 'John', 'JOHN', 'john'
SELECT * FROM users WHERE first_name ILIKE 'john%';

MySQL

In MySQL, LIKE is case-insensitive by default because the default collation (utf8mb4_general_ci) treats comparisons as case-insensitive. To force case-sensitive matching, use the BINARY keyword or a case-sensitive collation.

-- Case-insensitive by default in MySQL
SELECT * FROM users WHERE first_name LIKE 'john%';
-- Matches: John, john, JOHN

-- Force case-sensitive matching
SELECT * FROM users WHERE BINARY first_name LIKE 'john%';
-- Only matches: john

SQL Server

SQL Server's case sensitivity depends on the collation setting of the database or column. The default collation (SQL_Latin1_General_CP1_CI_AS) is case-insensitive. You can override this per query.

-- Case-sensitive comparison using COLLATE
SELECT * FROM users
WHERE first_name LIKE 'john%' COLLATE Latin1_General_CS_AS;

The safest approach across all databases: use LOWER() on both sides when you need guaranteed case-insensitive matching.

-- Works in every database
SELECT * FROM users WHERE LOWER(first_name) LIKE 'john%';

Keep in mind that wrapping a column in a function like LOWER() prevents the database from using an index on that column. PostgreSQL solves this with expression indexes, covered in the performance section below.

SIMILAR TO and Regex Matching (PostgreSQL)

When LIKE wildcards are not expressive enough, PostgreSQL offers SIMILAR TO and the ~ regex operator for advanced pattern matching.

SIMILAR TO

SIMILAR TO combines SQL LIKE syntax with regular expression elements. It supports | (alternation), * (zero or more), + (one or more), and character classes.

-- Match Gmail or Yahoo email addresses
SELECT * FROM users
WHERE email SIMILAR TO '%@(gmail|yahoo)\.com';

-- Match product codes starting with A or B, followed by 3 digits
SELECT * FROM products
WHERE sku SIMILAR TO '[AB][0-9]{3}';

POSIX Regex with ~ Operator

PostgreSQL also supports full POSIX regular expressions using the ~ (case-sensitive) and ~* (case-insensitive) operators.

-- Case-sensitive regex: names starting with J or K
SELECT * FROM users WHERE first_name ~ '^[JK]';

-- Case-insensitive regex
SELECT * FROM users WHERE first_name ~* '^[jk]';

-- Match a phone number pattern: (XXX) XXX-XXXX
SELECT * FROM contacts
WHERE phone ~ '^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$';

-- Extract domain from email using regex
SELECT email, (regexp_match(email, '@(.+)$'))[1] AS domain
FROM users;

MySQL REGEXP

MySQL uses the REGEXP (or RLIKE) keyword for regular expression matching.

-- Names starting with J or K
SELECT * FROM users WHERE first_name REGEXP '^[JK]';

-- Email addresses ending in .com or .org
SELECT * FROM users WHERE email REGEXP '\\.(com|org)$';

Regex-based matching is powerful but significantly slower than LIKE because it cannot use standard B-tree indexes. Use it only when simpler patterns are insufficient.

Performance: LIKE with Indexes

The placement of the wildcard determines whether your query can use an index.

Index-Friendly Patterns

Patterns with a fixed prefix followed by % can use a B-tree index because the database can narrow the search to a range of values.

-- Uses index on last_name (prefix match)
SELECT * FROM users WHERE last_name LIKE 'Smith%';

-- Uses index: fixed prefix + wildcard
SELECT * FROM products WHERE sku LIKE 'PRD-2026%';

Patterns That Force Full Scans

When the pattern starts with %, the database cannot use a B-tree index and must scan every row.

-- Full table scan: leading wildcard
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Full table scan: wildcard on both sides
SELECT * FROM users WHERE last_name LIKE '%smith%';

Solutions for Leading-Wildcard Searches

If you frequently need to search with leading wildcards, several strategies can help.

-- PostgreSQL: trigram index (pg_trgm extension)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users
USING gin (last_name gin_trgm_ops);

-- Now this query uses the trigram index
SELECT * FROM users WHERE last_name LIKE '%smith%';

-- PostgreSQL: expression index for ILIKE
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Full-text search (PostgreSQL)
CREATE INDEX idx_products_fts ON products
USING gin (to_tsvector('english', description));

SELECT * FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('wireless & keyboard');

For MySQL, consider using a FULLTEXT index for substring searches on large text columns. SQL Server offers Full-Text Search as a built-in feature that handles leading-wildcard patterns efficiently.

Real-World Examples

Email Filtering

-- Find all Gmail users
SELECT user_id, email FROM users
WHERE email LIKE '%@gmail.com';

-- Find users with potentially invalid emails (no @ sign)
SELECT user_id, email FROM users
WHERE email NOT LIKE '%@%.%';

-- Segment users by email provider
SELECT
    CASE
        WHEN email LIKE '%@gmail.com' THEN 'Gmail'
        WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'
        WHEN email LIKE '%@outlook.com' THEN 'Outlook'
        ELSE 'Other'
    END AS provider,
    COUNT(*) AS user_count
FROM users
GROUP BY provider
ORDER BY user_count DESC;

Name Search

-- Flexible name search for an autocomplete feature
SELECT first_name, last_name, email
FROM users
WHERE first_name ILIKE 'mar%'
   OR last_name ILIKE 'mar%'
ORDER BY last_name
LIMIT 10;

-- Find names with special characters (data cleanup)
SELECT * FROM users
WHERE first_name LIKE '%[^a-zA-Z ]%' -- SQL Server syntax
   OR first_name ~ '[^a-zA-Z ]';     -- PostgreSQL syntax

Phone Number Patterns

-- Find US phone numbers (area code starting with 212)
SELECT * FROM contacts WHERE phone LIKE '212%';

-- Find phone numbers stored in (XXX) XXX-XXXX format
SELECT * FROM contacts WHERE phone LIKE '(___) ___-____';

-- Find phone numbers missing area codes (less than 10 digits)
SELECT * FROM contacts
WHERE phone NOT LIKE '(___) ___-____'
  AND phone NOT LIKE '___-___-____';

Log and URL Analysis

-- Find all API error logs
SELECT * FROM logs
WHERE message LIKE '%ERROR%'
  AND endpoint LIKE '/api/%';

-- Find URLs with specific query parameters
SELECT * FROM page_views
WHERE url LIKE '%utm_source=google%';

-- Find file paths by extension
SELECT * FROM uploads
WHERE filename LIKE '%.pdf'
   OR filename LIKE '%.docx';

Frequently Asked Questions

What is the difference between % and _ wildcards in SQL?

The % wildcard matches any sequence of zero or more characters, while the _ wildcard matches exactly one single character. For example, 'J%' matches 'John', 'Jane', and even just 'J', but 'J_' only matches exactly two-character strings starting with J. Use % for broad pattern matching and _ when you need to match a specific number of characters.

Is the SQL LIKE operator case-sensitive?

It depends on your database. In PostgreSQL, LIKE is case-sensitive by default, so you need to use ILIKE for case-insensitive matching. In MySQL, LIKE is case-insensitive by default because of the default collation. In SQL Server, case sensitivity depends on the collation setting of the column or database. Always check your database's default behavior.

Does LIKE work with indexes?

LIKE can use a B-tree index only when the wildcard appears at the end of the pattern, such as 'abc%'. When the pattern starts with a wildcard like '%abc', the database must perform a full table scan. For better performance on leading-wildcard searches, consider using full-text search indexes or trigram indexes (pg_trgm in PostgreSQL).

How do I search for a literal % or _ character in a LIKE pattern?

Use the ESCAPE clause to define an escape character. For example: WHERE column LIKE '20\%' ESCAPE '\' will match the literal string '20%'. You can choose any character as your escape character. Some databases also support bracket notation like [%] in SQL Server to match a literal percent sign.