SQL LIKE Operator

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

# SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

## The LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

### SQL LIKE Syntax

    SELECT column_name(s)
    FROM table_name
    WHERE column_name LIKE pattern

## LIKE Operator Example

The "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

Now we want to select the persons living in a city that starts with "s" from the table above.

We use the following SELECT statement:

    SELECT * FROM Persons
    WHERE City LIKE 's%'

The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

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
3 | Pettersen | Kari | Storgt 20 | Stavanger

Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.

We use the following SELECT statement:

    SELECT * FROM Persons
    WHERE City LIKE '%s'

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 "tav" from the "Persons" table.

We use the following SELECT statement:

    SELECT * FROM Persons
    WHERE City LIKE '%tav%'

The result-set will look like this:

P_Id | LastName | FirstName | Address | City
--- | --- | --- | --- | ---
3 | Pettersen | Kari | Storgt 20 | Stavanger

It is also possible to select the persons living in a city that NOT contains the pattern "tav" from the "Persons" table, by using the NOT keyword.

We use the following SELECT statement:

    SELECT * FROM Persons
    WHERE City NOT LIKE '%tav%'

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

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