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

The SQL LIKE Operator

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

SQL LIKE Syntax

<div class="w3-code w3-border notranslate"><div>SELECT <em>column_name(s)</em>
FROM <em>table_name</em>
WHERE <em>column_name</em> LIKE <em>pattern</em>;</div><div>

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

SQL LIKE Operator Examples

The following SQL statement selects all customers with a City starting with the letter “s”:

Example

<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE 's%';</div>

Tip: The “%” sign is used to define wildcards (missing letters) both before and after the pattern. You will learn more about wildcards in the next chapter.

The following SQL statement selects all customers with a City ending with the letter “s”:

Example

<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE '%s';</div>

The following SQL statement selects all customers with a Country containing the pattern “land”:

Example

<div class="w3-code notranslate">SELECT * FROM Customers
WHERE Country LIKE '%land%';</div>

Using the NOT keyword allows you to select records that do NOT match the pattern.

The following SQL statement selects all customers with Country NOT containing the pattern “land”:

Example

<div class="w3-code notranslate">SELECT * FROM Customers
WHERE Country NOT LIKE '%land%';</div>
<div class="w3-code notranslate">
Pankaj Singh Sugara

Author Pankaj Singh Sugara

More posts by Pankaj Singh Sugara