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:
| 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 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
| 3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
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 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
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 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
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 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
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 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
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 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
| 3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
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 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
Start Creating a stunning, Flash website. It's easy and free!
Wix.com offers you a simple, powerful, drag & drop editing platform to create stunning Flash websites, layouts, and more.
With added e-commerce features such as search engine visibility and professional tools, Wix is the ultimate solution for creating a spectacular site.
 |
W3Schools' Online Certification Program
The perfect solution for professionals who need to balance work, family, and career building.
More than 4500 certificates already issued!
|
The HTML Certificate documents your knowledge of HTML, XHTML, and CSS.
The JavaScript Certificate documents your knowledge of JavaScript and HTML DOM.
The XML Certificate documents your knowledge of XML, XML DOM and XSLT.
The ASP Certificate documents your knowledge of ASP, SQL, and ADO.
The PHP Certificate documents your knowledge of PHP and SQL (MySQL).
| 
