SQL Wildcards

SQL Wildcard Characters

A wildcard character is used to replace one or more characters within a string.

Wildcard characters, utilized alongside the LIKE operator within a WHERE clause, aid in searching for a specific pattern within a column.

Example

Retrieve all customers whose names begin with the letter ‘a’.

SELECT * FROM Customers WHERE CustomerName LIKE ‘a%’;

Wildcard Characters

Symbol	Description
%	Denotes zero or more characters
_	Signifies a single character
[]	Within the brackets, * represents any single character.
^	Indicates any character not contained within the brackets.
–	Denotes any single character within the specified range.
{}	Denotes any escaped character.

*Not supported in PostgreSQL and MySQL databases.

** Supported exclusively in Oracle databases.

Demo Database

Here is an excerpt from the Customers table utilized in the examples:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Using the % Wildcard

The % wildcard signifies any number of characters, including zero characters.

Example

Retrieve all customers whose names end with the pattern ‘es’.

SELECT * FROM Customers WHERE CustomerName LIKE ‘%es’;

Example

Retrieve all customers whose names contain the pattern ‘mer’.

SELECT * FROM Customers WHERE CustomerName LIKE ‘%mer%’;

Using the _ Wildcard

The _ wildcard signifies a single character.

It can be any character or number, but each _ represents precisely one character.

Example

Retrieve all customers with a City starting with any character, followed by “ondon”.

SELECT * FROM Customers WHERE City LIKE ‘_ondon’;

Example

Retrieve all customers with a City starting with “L”, followed by any three characters, and ending with “on”.

SELECT * FROM Customers WHERE City LIKE ‘L___on’;

Using the [] Wildcard

The [ ] wildcard yields a result if any of the characters inside find a match.

Example

Retrieve all customers whose names start with either “b”, “s”, or “p”.

SELECT * FROM Customers WHERE CustomerName LIKE ‘[bsp]%’;

Using the – Wildcard

The – wildcard enables you to define a range of characters within the [ ] wildcard.

Example

Retrieve all customers whose names start with “a”, “b”, “c”, “d”, “e”, or “f”.

SELECT * FROM Customers WHERE CustomerName LIKE ‘[a-f]%’;

Combine Wildcards

Wildcards such as % and _ can be combined with each other.

Example

Retrieve all customers whose names start with “a” and are at least three characters long.

SELECT * FROM Customers WHERE CustomerName LIKE ‘a__%’;

Example

Retrieve all customers where “r” appears in the second position of their names.

SELECT * FROM Customers WHERE CustomerName LIKE ‘_r%’;

Without Wildcard

If no wildcard is specified, the phrase must exactly match to yield a result.

Example

Retrieve all customers who are from Spain.

SELECT * FROM Customers WHERE Country LIKE ‘Spain’;

Microsoft Access Wildcards

In addition to those mentioned, the Microsoft Access Database supports other wildcards.

Symbol	Description	Example
*	Denotes zero or more characters	bl* finds bl, black, blue, and blob
?	Signifies a single character	h?t finds hot, hat, and hit
[]	Denotes any single character contained within the brackets.	h[oa]t finds hot and hat, but not hit
!	Denotes any character not inside the brackets.	h[!oa]t finds hit, but not hot and hat
–	Denotes any single character within the specified range.	c[a-b]t finds cat and cbt
#	Indicates any single numeric character.	2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295