SQL LIKE – Pattern Matching with Wildcards

LIKE Syntax

The LIKE operator is used in a WHERE clause with a pattern string. The pattern can include literal characters and two special wildcard characters.

SQL

-- Basic syntax
SELECT * FROM table_name WHERE column LIKE 'pattern';

-- Both wildcards
-- %  →  zero or more characters (any characters)
-- _  →  exactly one character (any single character)

% – Zero or More Characters

The % wildcard matches any sequence of characters including an empty string. You can place it at the start, end, or both ends of the pattern.

SQL

-- Starts with 'A' (prefix search — can use index)
SELECT * FROM customers WHERE last_name LIKE 'A%';
-- Matches: 'Adams', 'Allen', 'Anderson', 'A'

-- Ends with '@gmail.com' (suffix search)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Matches: 'alice@gmail.com', 'bob123@gmail.com'

-- Contains 'john' anywhere (substring search — forces full table scan)
SELECT * FROM users WHERE username LIKE '%john%';
-- Matches: 'john', 'johnny', 'big_john', 'johnson'

-- Starts with 'invoice_' and ends with '.pdf'
SELECT * FROM files WHERE filename LIKE 'invoice\_%\.pdf' ESCAPE '\';
-- Escaping % and _ when you want the literal characters

-- Pattern with no wildcards behaves like = (exact match)
SELECT * FROM products WHERE name LIKE 'Laptop';
-- Same as: WHERE name = 'Laptop'

_ – Exactly One Character

The _ wildcard matches exactly one character — any character. Use it when you need to match a specific pattern length or position.

SQL

-- Exactly 2-character country codes
SELECT * FROM countries WHERE code LIKE '__';
-- Matches: 'US', 'GB', 'DE' — not 'USA' or 'U'

-- Phone numbers in format: 3 digits, dash, 4 digits
SELECT * FROM contacts WHERE phone LIKE '___-____';
-- Matches: '555-1234', '800-9999'

-- SKU starting with two letters, then 4 digits
SELECT * FROM products WHERE sku LIKE '__-____';
-- Matches: 'AB-1234', 'XY-9876'

-- Combining _ and %
-- Names starting with any character, then 'at', then anything
SELECT * FROM users WHERE name LIKE '_at%';
-- Matches: 'Cathy', 'Matthew', 'Natalie' (2nd+3rd chars are 'at')

NOT LIKE

NOT LIKE returns rows where the column does not match the pattern.

SQL

-- Exclude test/example email addresses
SELECT * FROM users WHERE email NOT LIKE '%@test.com';
SELECT * FROM users WHERE email NOT LIKE '%example%';

-- Exclude filenames with a certain prefix
SELECT * FROM files WHERE filename NOT LIKE 'temp_%';

-- Combined with other conditions
SELECT * FROM users
WHERE email NOT LIKE '%@test.com'
  AND email NOT LIKE '%@example.com'
  AND is_active = 1;

Case Sensitivity

The case sensitivity of LIKE depends on the database and the column's collation.

Database	Default LIKE behaviour	Case-insensitive option
MySQL	Case-insensitive (default collation: utf8mb4_general_ci)	Default (no change needed); use `COLLATE utf8mb4_bin` for case-sensitive
PostgreSQL	Case-sensitive	Use `ILIKE` for case-insensitive matching
SQLite	Case-insensitive for ASCII letters only	No native ILIKE; use `LOWER(col) LIKE LOWER(pattern)`

SQL

-- MySQL: case-insensitive by default
SELECT * FROM users WHERE name LIKE 'alice%';
-- Matches: 'alice', 'Alice', 'ALICE', 'AliceSmith'

-- PostgreSQL: case-sensitive by default
SELECT * FROM users WHERE name LIKE 'alice%';
-- Matches: 'alice', 'alicesmith' — does NOT match 'Alice' or 'ALICE'

-- PostgreSQL ILIKE: case-insensitive
SELECT * FROM users WHERE name ILIKE 'alice%';
-- Matches: 'alice', 'Alice', 'ALICE', 'AliceSmith'

-- SQLite / portable: use LOWER() for consistent case-insensitive search
SELECT * FROM users WHERE LOWER(name) LIKE 'alice%';

Performance Note – Leading Wildcards and Index Usage

⚠️

Leading % forces a full table scan

When a LIKE pattern starts with % (e.g., LIKE '%smith' or LIKE '%john%'), the database cannot use a regular B-tree index on that column — it must scan every row. On a large table, this can be very slow. A prefix search (LIKE 'smith%') CAN use an index because the leading characters are known. For full-text search within large datasets, use a dedicated full-text search feature: FULLTEXT indexes in MySQL, tsvector / tsquery in PostgreSQL, or an external search engine like Elasticsearch.

SQL

-- Can use index on last_name (prefix search)
SELECT * FROM customers WHERE last_name LIKE 'Smi%';
-- Index range scan: rows starting with 'Smi'

-- CANNOT use index (leading wildcard — full table scan)
SELECT * FROM customers WHERE last_name LIKE '%smith';
SELECT * FROM customers WHERE last_name LIKE '%mit%';

-- Check with EXPLAIN (MySQL)
EXPLAIN SELECT * FROM customers WHERE last_name LIKE 'Smi%';
-- Look for 'range' in the type column — good
EXPLAIN SELECT * FROM customers WHERE last_name LIKE '%smith';
-- Look for 'ALL' in the type column — full table scan

Summary

📋 Summary

LIKE pattern matches string columns using two wildcards: % (zero or more chars) and _ (exactly one char).
Prefix search (LIKE 'abc%') can use a B-tree index. Leading % (LIKE '%abc') forces a full table scan.
NOT LIKE returns rows that do not match the pattern.
MySQL is case-insensitive by default. PostgreSQL is case-sensitive — use ILIKE for case-insensitive matching in PostgreSQL.
For large-scale full-text search, use database full-text search features (FULLTEXT in MySQL, tsvector in PostgreSQL) instead of LIKE '%term%'.

FAQ

How do I search for a literal % or _ character with LIKE? +

Use the ESCAPE clause to define an escape character, then prefix the literal wildcard with it. For example: WHERE notes LIKE '50\% complete' ESCAPE '\' — this searches for the literal string "50% complete". Any character can be the escape character — backslash and ! are common choices. Example: LIKE '100!%' ESCAPE '!' finds "100%".

What is the difference between LIKE and regular expression matching? +

LIKE only supports two wildcards (% and _) and is part of the SQL standard. Regular expressions (REGEXP/RLIKE in MySQL, ~ in PostgreSQL) support full regex syntax — character classes, quantifiers, anchors, alternation. Regular expressions are much more expressive but also slower and non-standard. Use LIKE for simple prefix/suffix/substring checks and reserve regex for complex patterns like "email validation" or "extract digits".

Does LIKE work with NULL values? +

No — if a column value is NULL, col LIKE '%pattern%' returns UNKNOWN (not TRUE), and the row is filtered out. NOT LIKE also returns UNKNOWN for NULL values. This means NULL rows are excluded from both LIKE and NOT LIKE results. If you want to include NULL rows in a NOT LIKE result, add an explicit OR col IS NULL: WHERE name NOT LIKE '%test%' OR name IS NULL.

When should I use LIKE vs = for exact matching? +

For exact matches, always use =. A pattern with no wildcards (LIKE 'exact_value') behaves the same as = 'exact_value' functionally, but = is clearer, slightly faster (no pattern parsing), and unambiguously communicates your intent to readers. Use LIKE only when you actually need pattern matching with % or _.