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:

SQL Wildcard Examples

We have the following "Persons" table:

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:

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:

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:

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:

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:

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:

Learn how your website performs under various load conditions

WAPT is a load, stress and performance testing tool for websites and web-based applications. In contrast to "800-pound gorilla" load testing tools, it is designed to minimize the learning curve and give you an ability to create a heavy load from a regular workstation. WAPT is able to generate up to 3000 simultaneously acting virtual users using standard hardware configuration. Virtual users in each profile are fully customizable. Basic and NTLM authentication methods are supported. Graphs and reports are shown in real-time at different levels of detail, thus helping to manage the testing process. Download the free 30-day trial!

Jump to: Top of Page or HOME or Printer friendly page

W3Schools provides material for training only. We do not warrant the correctness of its contents. The risk from using it lies entirely with the user. While using this site, you agree to have read and accepted our terms of use and privacy policy.

Copyright 1999-2008 by Refsnes Data. All Rights Reserved.

W3Schools was converted to XHTML in December 1999