SQL LIKE and NOT LIKE Operators

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.

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.

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.

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

SELECT *
FROM Customers
WHERE country LIKE 'U_';

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