SQL LIKE and Wildcards

LIKE matches string values against a pattern. It’s the SQL operator for flexible text searching — useful for prefix matching, suffix matching, and contains searches.

Wildcard Characters

LIKE uses two wildcard characters:

Wildcard	Matches
`%`	Any sequence of characters (including empty)
`_`	Exactly one character

Common LIKE Patterns

-- Starts with 'J' (any number of chars after)
WHERE name LIKE 'J%'
-- Matches: John, Jane, Jennifer, J, Joe

-- Ends with '.com'
WHERE email LIKE '%.com'
-- Matches: alice@example.com, bob@test.com

-- Contains 'pro' anywhere (leading % is slow: no index use)
WHERE name LIKE '%pro%'
-- Matches: Pro Keyboard, MacBook Pro, ProPlus

-- Exactly 5 characters
WHERE code LIKE '_____'
-- Matches: AB123, XY987, HELLO

-- Second character is 'a'
WHERE name LIKE '_a%'
-- Matches: Gary, Mary, cat, table

-- Pattern with both wildcards
WHERE product_code LIKE 'KB-___-2025'
-- Matches: KB-MEC-2025, KB-MBR-2025

NOT LIKE

-- Exclude test email addresses
WHERE email NOT LIKE '%@test.com'
WHERE email NOT LIKE '%@example.com'

-- Names that don't start with 'test'
WHERE username NOT LIKE 'test%'

-- Exclude archived records
WHERE status NOT LIKE '%archived%'

Case Sensitivity

LIKE is case-sensitive in PostgreSQL and case-insensitive in MySQL (depending on collation).

PostgreSQL:

-- Case-sensitive: 'Apple' != 'apple'
WHERE name LIKE 'apple%'   -- won't match 'Apple'

-- Case-insensitive: use ILIKE (PostgreSQL extension)
WHERE name ILIKE 'apple%'  -- matches 'Apple', 'APPLE', 'apple'

-- Or convert to lowercase
WHERE LOWER(name) LIKE '%apple%'  -- portable across all databases

MySQL:

-- MySQL LIKE is case-insensitive by default (most collations)
WHERE name LIKE 'apple%'   -- matches 'Apple', 'APPLE', 'apple'

-- For case-sensitive matching in MySQL:
WHERE name LIKE BINARY 'apple%'

Escaping Wildcards

To match a literal % or _ character, escape it:

-- Match a literal percent sign (50% discount)
WHERE description LIKE '%50\%%' ESCAPE '\'

-- Match a literal underscore
WHERE code LIKE 'A\_B%' ESCAPE '\'
-- Matches: A_Banana, A_Bold, but not AABanana

-- PostgreSQL also supports standard ESCAPE syntax
WHERE filename LIKE '%\_%' ESCAPE '\'  -- contains underscore

Performance: When LIKE Uses an Index

B-tree indexes (the default) can support LIKE — but only when the wildcard appears at the END:

-- Can use index (wildcard at end):
WHERE name LIKE 'Smith%'     -- fast
WHERE sku LIKE 'KB-%'        -- fast

-- Cannot use index (wildcard at start):
WHERE name LIKE '%Smith'     -- full table scan
WHERE name LIKE '%Smith%'    -- full table scan

For contains-type searching (%keyword%) on large tables, consider:

PostgreSQL trigram index:

-- Install extension
CREATE EXTENSION pg_trgm;

-- Create GIN index for fast LIKE/ILIKE anywhere
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Now this can use the index:
WHERE name ILIKE '%keyboard%'

Full-text search (for natural language searching in large text columns):

-- PostgreSQL full-text search
WHERE to_tsvector('english', description) @@ to_tsquery('keyboard & wireless')

-- Add a GIN index on the tsvector
CREATE INDEX idx_products_fts ON products USING GIN (to_tsvector('english', description));

SIMILAR TO (SQL Standard)

PostgreSQL supports SIMILAR TO — a mix between LIKE and regular expressions:

-- Matches 'cat' or 'dog' (SQL regex alternation)
WHERE animal SIMILAR TO '(cat|dog)'

-- Matches words starting with a vowel
WHERE name SIMILAR TO '[AEIOU]%'

SIMILAR TO is slower than LIKE and less powerful than full regex. In practice, use LIKE for simple patterns and the ~ operator (PostgreSQL regex) for complex ones.

Regular Expression Matching (PostgreSQL)

-- ~ operator: regex match (case-sensitive)
WHERE email ~ '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$'

-- ~* operator: case-insensitive regex
WHERE name ~* '(smith|jones|brown)'

-- !~ : does not match (case-sensitive)
WHERE phone !~ '^\+1'  -- phone doesn't start with +1

Regex is powerful but slow on large tables without specialized indexes.

Practical Examples

Search for customers by partial name:

SELECT customer_id, name, email
FROM customers
WHERE name ILIKE '%smith%'
ORDER BY name;

Find SKUs matching a pattern:

SELECT sku, name, price
FROM products
WHERE sku LIKE 'KB-%-2025'  -- Keyboard products from 2025
ORDER BY sku;

Exclude internal/test accounts:

SELECT user_id, email, created_at
FROM users
WHERE email NOT LIKE '%@internal.company.com'
  AND email NOT LIKE '%@test.com'
  AND email NOT ILIKE '%test%'
ORDER BY created_at DESC;

Find products with a specific suffix in the model number:

SELECT * FROM products
WHERE model_number LIKE '%-PRO'
   OR model_number LIKE '%-ULTRA';