SQL LIKE and NOT LIKE Operators

In this tutorial, we'll learn about the LIKE operator in SQL and how to use them with examples.

SQL LIKE Operator

The LIKE operator in SQL is used with the WHERE clause to get a result set that matches the given string pattern. For example,

SELECT *
FROM Customers
WHERE country LIKE 'UK';

Here, the SQL command selects customers whose country is UK.

How to use LIKE operator in SQl
Example: SQL LIKE

Note: Although the LIKE operator behaves similar to the = operator in this example, they are not the same. The = operator is used to check equality whereas LIKE operator is used to match string patterns only.


SQL LIKE With Wildcards

The LIKE operator in SQL is often used with wildcards to match a pattern of string. For example,

SELECT *
FROM Customers
WHERE last_name LIKE 'R%';

Here, % (means zero or more characters) is a wildcard character. Hence, the SQL command selects customers whose last_name starts with R followed by zero or more characters after it.

What are wildcards in SQL
Example: SQL Wildcards

Example Two: SQL LIKE With Wildcards

There are more wildcard characters we can use. Lets see another example using _ wildcard character with LIKE in SQL.

SELECT *
FROM Customers
WHERE country LIKE 'U_';

Here, the SQL command selects customers whose country name starts with U and is followed by only one character.


SQL NOT LIKE Operator

We can also invert the working of LIKE operator and ignore the result set matching with the given string pattern by using the NOT operator. For example,

SELECT *
FROM Customers
WHERE country NOT LIKE 'USA';

Here, the SQL command selects all customers except those, whose country is USA.


SQL LIKE With Multiple Values

We can use the LIKE operator with multiple string patterns to select rows by using with the OR operator. For example,

SELECT *
FROM Customers
WHERE last_name LIKE 'R%t' OR last_name LIKE '%e';

Here, the SQL command selects customers whose last_name starts with R and ends with t, or customers whose last_name ends with e.


More SQL LIKE and NOT LIKE Examples

Find customers whose last name starts with R.
SELECT *
FROM Customers
WHERE last_name LIKE 'R%';
Find customers whose country is exactly one character after U.
SELECT *
FROM Customers
WHERE country LIKE 'U_';
Find customers whose last name starts with R and ends with t.
SELECT *
FROM Customers
WHERE last_name LIKE 'R%t';
Did you find this article helpful?