SQL — Wildcards

What are Wildcards?

Wildcards are special characters used with LIKE to search for patterns in text data instead of exact matches.

Wildcard Characters

Character	Description	Example
%	Zero or more characters	'A%' matches Alice, Adam, ABC
_	Exactly one character	'_at' matches Cat, Bat, Hat
[]	Any character in brackets	'[ABC]%' matches Alice, Bob
[^]	Any character NOT in brackets	'[^A]%' matches Bob, Tom

% — Match Multiple Characters

-- Names starting with 'A'
SELECT * FROM employees WHERE name LIKE 'A%';

-- Names ending with 'son'
SELECT * FROM employees WHERE name LIKE '%son';

-- Names containing 'art'
SELECT * FROM employees WHERE name LIKE '%art%';

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

_ — Match Single Character

-- Three-letter names starting with 'B'
SELECT * FROM employees WHERE name LIKE 'B__';

-- Product codes like 'P001', 'P002'
SELECT * FROM products WHERE code LIKE 'P___';

Combining Wildcards

-- Names with 'a' as second letter
SELECT * FROM employees WHERE name LIKE '_a%';

-- Emails with exactly one character before @
SELECT * FROM users WHERE email LIKE '_%@%.com';

LIKE vs =

-- Exact match
WHERE name = 'Alice'

-- Pattern match
WHERE name LIKE 'Ali%'  -- matches Alice, Alison, etc.

LIKE with NOT

-- Names NOT starting with 'A'
SELECT * FROM employees WHERE name NOT LIKE 'A%';

-- Emails NOT from gmail
SELECT * FROM users WHERE email NOT LIKE '%@gmail.com';

Case Sensitivity

Depends on the database:

-- MySQL: case-insensitive by default
WHERE name LIKE 'a%'

-- PostgreSQL: case-sensitive by default
WHERE name ILIKE 'a%'  -- case-insensitive

-- PostgreSQL with LOWER
WHERE LOWER(name) LIKE 'a%'

Bracket Wildcards (SQL Server)

-- Names starting with A, B, or C
WHERE name LIKE '[ABC]%'

-- Names NOT starting with A, B, or C
WHERE name LIKE '[^ABC]%'

Escaping Special Characters

If you need to search for % or _ literally:

-- MySQL: use backslash
WHERE name LIKE '100\%'

-- Standard SQL: use ESCAPE clause
WHERE name LIKE '100!%' ESCAPE '!'

Performance Warning

LIKE queries with a leading wildcard ('%text') can't use indexes and are slow on large tables:

-- Slow: full table scan
WHERE name LIKE '%son'

-- Fast: can use index
WHERE name LIKE 'John%'

For full-text search, use FULLTEXT indexes instead.

Practical Example

-- Find valid phone numbers
SELECT * FROM contacts
WHERE phone LIKE '+%';

-- Find usernames with special characters
SELECT * FROM users
WHERE username LIKE '%@%'
OR username LIKE '%#%'
OR username LIKE '%$%';

-- Find product codes in a range
SELECT * FROM products
WHERE code LIKE 'PROD-2025-___';

Best Practices

• Use LIKE only when you need pattern matching — = is faster for exact matches
• Put the wildcard at the end when possible ('text%' not '%text')
• Use ILIKE (PostgreSQL) or LOWER() for case-insensitive matching
• Consider FULLTEXT search for complex text searches
• Avoid leading wildcards on large tables

Practice

1. Find all customers whose names start with 'J'
2. Find all products with 4-digit SKUs
3. Find all emails from specific domains
4. Find usernames that contain exactly 5 characters