SQL Wildcards

SQL

SQL Wildcards : SQL wildcards characters is used to substitute any other character(s) in a string.

  • Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

  • % – The percent sign represents zero, one, or multiple characters
  • _ – The underscore represents a single character

Note:

MS Access uses a question mark (?) instead of the underscore (_).

In MS Access and SQL Server you can also use:

  • [charlist] – Defines sets and ranges of characters to match
  • [^charlist] or [!charlist] – Defines sets and ranges of characters NOT to match

The wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’ Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%_%’ Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ Finds any values that starts with “a” and ends with “o”

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

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 following SQL statement selects all customers with a City starting with “ber”:

Example

SELECT * FROM Customers
WHERE City LIKE ‘ber%’;

Output

Number of Records: 3

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland
49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy

 

The following SQL statement selects all customers with a City containing the pattern “es”:

Example

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

 

Output

Number of Records: 9

CustomerID CustomerName ContactName Address City PostalCode Country
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
40 La corne d’abondance Daniel Tonini 67, avenue de l’Europe Versailles 78000 France
50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium
54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina
64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil

 

Using the _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by “erlin”:

Example

SELECT * FROM Customers
WHERE City LIKE ‘_erlin’;

 

Output

Number of Records: 1

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

 

The following SQL statement selects all customers with a City starting with “L”, followed by any character, followed by “n”, followed by any character, followed by “on”:

Example

SELECT * FROM Customers
WHERE City LIKE ‘L_n_on’;

Output

Number of Records: 6

CustomerID CustomerName ContactName Address City PostalCode Country
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK
53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK

 

Using the [charlist] Wildcard

The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

Example

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

The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

Output

Number of Records: 29

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland
15 Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo 05432-043 Brazil
21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil
24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden
29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain
30 Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain
35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela
39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany
45 Let’s Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA
46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela
48 Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland 97219 USA
49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy
50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium
54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina
57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France
59 Piccolo und mehr Georg Pipps Geislweg 14 Salzburg 5020 Austria
62 Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891 São Paulo 05487-020 Brazil
64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina
70 Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78 Stavern 4110 Norway
71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA
74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France
77 The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland 97201 USA
78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA
81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil
86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany
89 White Clover Markets Karl Jablonski 305 – 14th Ave. S. Suite 3B Seattle 98128 USA

 

Example

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

 

Output

Number of Records: 22

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland
17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden
29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain
31 Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas 04876-786 Brazil
33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela
37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork Ireland
38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany
46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela
49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy
50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium
54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina
55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA
63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany
64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina
65 Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque 87110 USA
71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA
76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium
78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA

 

Using the [!charlist] Wildcard

The two following SQL statements selects all customers with a City NOT starting with “b”, “s”, or “p”:

Example

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

 

Output

Number of Records: 2

CustomerID CustomerName ContactName Address City PostalCode Country
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

 

Or:

Example

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

Output

Number of Records: 62

CustomerID CustomerName ContactName Address City PostalCode Country
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