Monday, October 19, 2015

SQL Wildcard Characters

SQL Wildcard Characters

In SQL, wildcard characters are used with the SQL LIKE operator.
SQL wildcards are used to search for data within a table. 
With SQL, the wildcards are:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for a single character
[charlist] Sets and ranges of characters to match
[^charlist]
or
[!charlist]		 Matches only a character NOT specified within the brackets

Demo Database

In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:

ustomerID 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 SQL % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

Example

SELECT * FROM Customers
WHERE City LIKE 'ber%'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
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%'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
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 
 
 

Using the SQL _ 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'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
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';
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
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 
 
 The following SQL statement selects all customers with a City starting with "b", "s", or "p":

Example

SELECT * FROM Customers
WHERE City LIKE '[bsp]%'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
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 
 The following SQL statement selects all customers with a City starting with "a", "b", or "c":

Example

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
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 
 
The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":

Example

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

or

SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
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 
6  Blauer See Delikatessen  Hanna Moos  Forsterstr. 57  Mannheim  68306  Germany 
 
Posted by at
Labels: , , , ,