SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator with WHERE clause to search for a specified pattern in a column.

Example:

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

The above example shows - get all the customers whose name starts with 'a'

Wildcard characters:

Symbol	Description
%	Represents zero or more characters
_	Represents a single character
[]	Represents any single character within the brackets *
^	Represents any character not in the brackets *
-	Represents any single character within the specified range *
{}	Represents any escaped character **

Using the % Wildcard

The % wildcard represents any number of characters, even zero characters.

Return all customers that ends with the pattern 'es':

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

Using the _ Wildcard

The _ wildcard represents a single character.

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

Return all customers with a City starting with any character, followed by "ondon":

SELECT * FROM Customers
WHERE City LIKE '_ondon';

Using the [] Wildcard

The [] wildcard returns a result if any of the characters inside gets a match.

Return all customers starting with either "b", "s", or "p":

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

Using the - Wildcard

The - wildcard allows you to specify a range of characters inside the [] wildcard.

Return all customers starting with "a", "b", "c", "d", "e" or "f":

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