The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
%) represents zero, one, or multiple characters._) represents one, single character.Note: We will cover wildcards in more detail in the next chapter.
SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
Below is a selection from the Customers table:
Customers Table:
| CustomerID | CustomerName | City |
|---|---|---|
| 1 | Alfreds Futterkiste | Berlin |
| 2 | Ana Trujillo Emparedados | México D.F. |
| 3 | Antonio Moreno Taquería | México D.F. |
| 4 | Berglunds snabbköp | Luleå |
| 5 | Blauer See Delikatessen | Mannheim |
LIKE Operator ExamplesThe following table shows some examples of LIKE operators with % and _ wildcards:
| Operator | Description |
|---|---|
WHERE CustomerName LIKE 'a%' |
Finds any values that start with "a" |
WHERE CustomerName LIKE '%a' |
Finds any values that end with "a" |
WHERE CustomerName LIKE '%or%' |
Finds any values that have "or" in any position |
WHERE CustomerName LIKE '_r%' |
Finds any values that have "r" in the second position |
WHERE CustomerName LIKE 'a__%' |
Finds any values that start with "a" and are at least 3 characters in length |
WHERE ContactName LIKE 'a%o' |
Finds any values that start with "a" and ends with "o" |
The following SQL statement selects all customers with a CustomerName starting with "a":
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
The following SQL statement selects all customers with a CustomerName ending with "a":
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
NOT LIKEYou can also use the NOT keyword to find records that do not match the pattern. The following SQL statement selects all customers that do not start with "a":
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';