SQL Wildcards

From http://www.w3schools.com (Copyright Refsnes Data)

# 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

From http://www.w3schools.com (Copyright Refsnes Data)