SQL Wildcards

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:

WildcardDescription
%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_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger


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_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes

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_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes


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_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes

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_IdLastNameFirstNameAddressCity
2SvendsonToveBorgvn 23Sandnes


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_IdLastNameFirstNameAddressCity
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger

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_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes

No comments: