SQL Wildcards

« Previous Chapter Next Chapter »

SQL wildcards can be used when searching for data in a database.

SQL Wildcards 

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist]

or

[!charlist]

 Any single character not in charlist

SQL Wildcard Examples

We have the following "Persons" table:

P_Id LastName FirstName Address City
1 Karmen Kasa Los Angels - USA New Orleans
2 Jacob Mary Los Angels - USA New Orleans
3 Maxwell Glen Australia Sydney

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE 'sa%'

The result-set will look like this:

P_Id LastName FirstName Address City
1 Karmen Kasa Los Angels - USA New Orleans
2 Jacob Mary Los Angels - USA New Orleans

Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE '%nes%'

The result-set will look like this:

P_Id LastName FirstName Address City
1 Karmen Kasa Los Angels - USA New Orleans
2 Jacob Mary Los Angels - USA New Orleans

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName LIKE '_la'

The result-set will look like this:

P_Id LastName FirstName Address City
1 Karmen Kasa Los Angels - USA New Orleans

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on'

The result-set will look like this:

P_Id LastName FirstName Address City
2 Jacob Mary Los Angels - USA New Orleans

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'

The result-set will look like this:

P_Id LastName FirstName Address City
2 Jacob Mary Los Angels - USA New Orleans
3 Maxwell Glen Australia Sydney

Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'

The result-set will look like this:

P_Id LastName FirstName Address City
1 Karmen Kasa Los Angels - USA New Orleans

« Previous Chapter Next Chapter »
Have Any Suggestion? We Are Waiting To Hear from YOU!

Your Query was successfully sent!