SQL Wildcards wildcards

Wildcards are used to replace any other characters in the string.

Wildcards are used with SQL LIKE operators. U se theLIKE operator in the WHERE clause to search for the specified pattern in the column.

There are two wildcards used with the LIKE operator:

  • ％ - A percent sign represents zero, one or more characters
  • _ - Underscores represent a single character

Attention:

  • MS Access uses an * (-) wildcard instead of a percentage % wildcard.
  • MS Access uses a question mark ? i nstead of underscoreing _

In MS Access and SQL Server, you can also use:

  • Charlist - Defines the collection and range of characters to match
  • (charlist) or .! Charlist - Defines a collection and range of mismatched characters

Wildcards can also be combined!

Here are some examples of using the ％ _ '' wildcards to display differentLIKE operators:

LIKE operator Describe
WHERE CustomerName LIKE 'a%' Look for any value that starts with "a"
WHERE CustomerName LIKE '%a' Look for any value that ends with "a"
WHERE CustomerName LIKE '%or%' Look for any value with "or" anywhere
WHERE CustomerName LIKE '_r%' Look for any values with "r" in the second position
WHERE CustomerName LIKE 'a_%_%' Look for values that begin with "a" and are at least 3 characters long
WHERE ContactName LIKE 'a%o' Look for any value that starts with "a" and ends with "o"

Demonstrate the database

In this tutorial, we'll use the famous Northwind sample database.

Here's the data from the Customers table:

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

Use sql % wildcards

The following SQL statement selects all customer City to begin with the letter "ber":

SELECT * FROM Customers
WHERE City LIKE 'ber%';

The following SQL statement selects all customers in City that contain the "es" pattern:

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

Use the SQL-wildcard

The following SQL statement selects City to start with any character, followed by "erlin" for all customers:

SELECT * FROM Customers
WHERE City LIKE '_erlin';

The following SQL statement selects City to start with "L", followed by any character, followed by "n", followed by any character, followed by all customers with "on":

SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

Use the SQL (charlist) wildcard

The following SQL statement selects all customer City to start with "b," "s," or "p":

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

The following SQL statement selects all customers that "City" begins with "a," "b," or "c":

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

The following SQL statement selects that all customer City does not start with "b," "s," or "p":

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