SQL LIKE and NOT LIKE Operators

SQL LIKE Operator

The SQL LIKE operator is used with the WHERE clause to get a result set that matches the given string pattern. It has the following syntax:

SELECT column1, column2, ...
FROM table
WHERE column LIKE value;

Here,

• column1,column2, ... are the columns to select the data from
• table is the name of the table
• column is the column we want to apply the filter to
• LIKE matches the column with value
• value is the pattern you want to match in the specified column

For example,

-- select customers who live in the UK 
SELECT *
FROM Customers
WHERE country LIKE 'UK';

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

Note: Although the LIKE operator behaves similarly 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 SQL LIKE query is often used with wildcards to match a pattern of string. For example,

-- SQL LIKE query with wildcard
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.

Example: SQL LIKE With Wildcards

There are more wildcard characters we can use with LIKE. Let's look at another example using the _ wildcard character.

-- select customers whose country names start with U followed by a single character
SELECT *
FROM Customers
WHERE country LIKE 'U_';

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

SQL NOT LIKE Operator

We can also invert the working of the LIKE operator by using the NOT operator with it. This returns a result set that doesn't match the given string pattern.

The SQL NOT LIKE operator has the following syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column NOT LIKE value;

Here,

• column1,column2, ... are the columns to select the data from
• table_name is the name of the table
• column is the column we want to apply the filter to
• NOT LIKE ignores the match of the column with the value
• value is the pattern you don't want to match in the specified column

For example,

-- select customers who don't live in the USA
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 using the OR operator. For example,

-- select customers whose last_name starts with R and ends with t
-- or customers whose last_name ends with e 
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.