You can use SQL wildcard characters when searching for data in a database.
SQL wildcard characters
The SQL wildcard can override one or more characters when searching for data in a database.
SQL wildcard characters must be used with the LIKE operator.
In SQL, you can use the following wildcard characters:
wildcard characters |
Description |
% |
Override one or more characters |
_ |
Replaces only one character |
[Charlist] |
Any single character of the word columns |
[^charlist] Or [!charlist] |
Any single character that is not in the word columns |
The 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 |
Use a% wildcard character
Example 1
Now, we want to choose from the "Persons" table above for people living in cities that start with "Ne":
We can use the following SELECT statement:
SELECT * from Personswhere city like ' ne% '
Result set:
Id |
LastName |
FirstName |
Address |
| City
2 |
Bush |
George |
Fifth Avenue |
New York |
Example 2
Next, we want to select from the "Persons" table the people who live in cities that contain "Lond":
We can use the following SELECT statement:
SELECT * from Personswhere city like '%lond% '
Result set:
Id |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
Using _ Wildcard characters
Example 1
Now, we want to select the first character of the name from the "Persons" table above and then the person who is "eorge":
We can use the following SELECT statement:
SELECT * from Personswhere FirstName like ' _eorge '
Result set:
Id |
LastName |
FirstName |
Address |
| City
2 |
Bush |
George |
Fifth Avenue |
New York |
Example 2
Next, we want the last name of the record selected from the "Persons" table to start with "C", then an arbitrary character, then "R", then any character, then "ER":
We can use the following SELECT statement:
SELECT * from Personswhere LastName like ' c_r_er '
Result set:
Id |
LastName |
FirstName |
Address |
| City
3 |
Carter |
Thomas |
Changan Street |
Beijing |
Using [charlist] wildcard characters
Example 1
Now, we would like to select from the "Persons" table above the person who lives in the city that begins with "A" or "L" or "N":
We can use the following SELECT statement:
SELECT * from Personswhere 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 from the "Persons" table above the people who live in cities that do not start with "A" or "L" or "N":
We can use the following SELECT statement:
SELECT * from Personswhere city like ' [! aln]% '
Result set:
Id |
LastName |
FirstName |
Address |
| City
3 |
Carter |
Thomas |
Changan Street |
Beijing |
wildcard characters for SQL