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 use Wildcard are : 

%,_,[^charlist] or [!charlist]

% : A substitute for zero or more characters

_ : A substitute for exactly one character

[^charlist] or [!charlist] : Any single character not in charlist

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 'Chi%'

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 '%ago%'

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 '_om'

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 'K_end_en'

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 "a" or "k"  from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[ak]%'

The result-set will look like this:

Next, we want to select the persons with a last name that do not start with "a" or "k" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[!ak]%'

The result-set will look like this:

SQL Wildcards