wildcard characters for SQL

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:

City

Id	LastName	FirstName	Address
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: City

Id	LastName	FirstName	Address
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: City

Id	LastName	FirstName	Address
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: City

Id	LastName	FirstName	Address
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: City

Id	LastName	FirstName	Address
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: City

Id	LastName	FirstName	Address
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: City

Id	LastName	FirstName	Address
3	Carter	Thomas	Changan Street	Beijing

wildcard characters for SQL