You can use SQL wildcard characters when searching for data in a database.
SQL Wildcards SQL wildcards can replace one or more characters when searching for data in a database.
SQL wildcards must be used with the LIKE operator.
In SQL, the following wildcard characters can be used:
wildcard description
% replaces one or more characters
_ replaces only one character
[charlist] Any single character in a character list
[^charlist]
or
[!charlist]
Any single character not in the character column
Original table (used in the example):
Persons table:
Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
Using % wildcard example 1
Now, we want to select people from the "Persons" table above who live in cities starting with "Ne":
We can use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 'Ne%' result set:
Id LastName FirstName Address City
2 Bush George Fifth Avenue New York
Example 2
Next, we want to select people from the "Persons" table who live in the city containing "lond":
We can use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%lond%' result set:
Id LastName FirstName Address City
1 Adams John Oxford Street London
Using _ wildcard example 1
Now, we want to select people from the "Persons" table above who have the first character of their name followed by "eorge":
We can use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName LIKE '_eorge' Result set:
Id LastName FirstName Address City
2 Bush George Fifth Avenue New York
Example 2
Next, we want to select a record from the "Persons" table whose last name begins with "C", then any character, then "r", then any character, then "er":
We can use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er' Result set:
Id LastName FirstName Address City
3 Carter Thomas Changan Street Beijing
Using [charlist] wildcard example 1
Now, we want to select people whose city starts with "A" or "L" or "N" from the "Persons" table above:
We can use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '[ALN]%' Result set:
Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
Example 2
Now, we want to select people from the "Persons" table above who live in a city that does not start with "A" or "L" or "N":
We can use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '[!ALN]%' Result set:
Id LastName FirstName Address City
3 Carter Thomas Changan Street