SQL Wildcard Characters and the LIKE Operator

Wildcard characters are used in SQL to perform partial string matching within the WHERE clause of a query. They greatly expand the flexibility of your searches, enabling you to find rows where a string column matches a pattern rather than requiring an exact match.

Using Wildcards with LIKE

Wildcard characters are used with the LIKE operator to create flexible search patterns. The most common wildcards are % and _, but additional wildcards are available in some database systems (like MS Access).

Standard Wildcards

Symbol	Description	Example
`%`	Matches any sequence of zero or more characters.	`'a%'` matches "apple", "avocado", "a", etc.
`_`	Matches any single character.	`'a_ple'` matches "apple", but not "aple" or "applepie".
`[...]`	Matches any single character within the brackets.	`'a[aeiou]ple'` matches "apple", "able", etc. but not "applepie".
`[^...]`	Matches any single character not within the brackets.	`'a[^aeiou]ple'` matches "applepie" but not "apple" or "able".
`[a-z]`	Matches any single character within the specified range.	`'a[a-z]ple'` matches "apple", "able", etc.

Note: [...], [^...], and range matching ([a-z]) are not supported in all database systems (PostgreSQL and MySQL do not support them).

Examples Using Wildcards

Starts With "a"

This example selects customers whose names begin with 'a'. (Assumes a 'Customers' table with a 'CustomerName' column.)

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

Output


(Customers whose names start with 'a' will be returned.)

Ends With "es"

This selects customers whose names end with 'es'.

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE '%es';

Output


(Customers whose names end with 'es' will be returned.)

Contains "mer"

This selects customers whose names contain 'mer'.

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE '%mer%';

Output


(Customers whose names contain 'mer' will be returned.)

Using the Underscore Wildcard

The underscore (_) matches any single character.

Syntax


SELECT * FROM Customers WHERE City LIKE '_ondon'; --City starting with any character followed by 'ondon'
SELECT * FROM Customers WHERE City LIKE 'L___on'; --City starting with 'L', then three characters, then 'on'

Output


(Customers whose cities match the specified patterns will be returned.)

Using the Brackets Wildcard

The brackets ([...]) match any of the characters inside.

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE '[bsp]%'; --Names starting with 'b', 's', or 'p'

Output


(Customers whose names start with 'b', 's', or 'p' will be returned.)

Using the Range Wildcard

The hyphen (-) within brackets specifies a range of characters.

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE '[a-f]%'; --Names starting with 'a', 'b', 'c', 'd', 'e', or 'f'

Output


(Customers whose names start with 'a', 'b', 'c', 'd', 'e', or 'f' will be returned.)

Combining Wildcards

You can combine wildcards to create complex patterns.

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE 'a__%'; --Names starting with 'a' and at least 3 characters long
SELECT * FROM Customers WHERE CustomerName LIKE '_r%'; --Names with 'r' in the second position

Output


(Customers matching the specified patterns will be returned.)

Exact Match

No wildcards; requires an exact match.

Syntax


SELECT * FROM Customers WHERE Country LIKE 'Spain';

Output


(Customers from Spain will be returned.)

Microsoft Access Wildcards

MS Access uses slightly different wildcard characters:

Symbol	Description	Example
`*`	Zero or more characters.	`bl*` finds "bl", "black", "blue", etc.
`?`	Any single character.	`h?t` finds "hot", "hat", "hit", etc.
`[...]`	Any single character within the brackets.	`h[oa]t` finds "hot" and "hat".
`[!...]`	Any character NOT in the brackets.	`h[!oa]t` finds "hit".
`[a-z]`	Any character in the specified range.	`c[a-b]t` finds "cat" and "cbt".
`#`	Any single digit.	`2#5` finds "205", "215", etc.