Learn From - Interactive SQL


Next Chapter »

SQL Wildcards


SQL Wildcards are used widely to search for records in database tables. It is used as a substitute for one or more characters in the search.

SQL Wildcards are used along with the Like operator in the Where clause.

SQL Wildcards can be used with the options given in the table below

Wildcard Description
% Used instead of one or more characters
_ Used instead of one character
[characters] Provide a list that may be one or more characters
[^characters] Provide the list of charecters to omit

SQL Wildcard Example


Below is shown the "Customers" table:

CustomerID CustomerName EmailID Address City PostalCode Country
C001 Fredrick Johnson FJohnson@yahoo.com Berkeley Gardens 12 Brewery London WX1 6LT UK
C002 Mary Davidson Mary@gmail.com 35 King George Madrid 28023 Spain
C003 George Clooney George@hotmail.com Obere Str. 57 Berlin 12209 Germany
C004 David Louis David@mail.com 2743 Bering St. NewJersy 07019 USA
C005 Johnny Anderson Johnny@gmail.com 55 Grizzly Peak Rd. NewJersy 07019 USA
C006 Paul Henriot PaulHenriot@yahoo.com 59 rue de l'Abbaye Paris 51100 France
C007 Henry King Henry@mail.com 120 Hanover Sq. London WA1 1DP UK
C008 Robert John Robert@gmail.com 87 Polk St. Suite 5 San Francisco 94117 USA

Type or Copy the SQL into the Text Editor, click the "View Result" button, to see the result.

To retrieve records from the Customers table with EmailID column having "yahoo" in it.

Select * from Customers
where EmailID like '%yahoo%';

To retrieve records from Customers table with Country column that has first two charecters as "US".

Select * from Customers
where Country like 'US_';

To retrieve all the records from the Customers table, where the City column name has second and fifth alphabets as any charecter.

SELECT * FROM Customers
WHERE City LIKE 'N_wJ_rsy';

To retrieve all the records from the Customers table, where the City column name starts with "N" or "B".

Select * from Customers
where City like '[NB]%';

To retrieve all the records from the Customers table, where the City column name does not start with "N" or "B".

Select * from Customers where City like '[^NB]%';

SQL in Text Editor:








Next Chapter »