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 the% 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 the _ Wildcard 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 |
Use the [charlist] wildcard character Example 1
Now, we want to select the city of residence from the "Persons" table above with "A" or "L" or
SQL Server:sql wildcard characters