SQL Like - SQL Not Like

SQL Like operator can be used with any query with where clause. So we can use it with Select, Delete, Update etc.

SELECT column FROM table_name WHERE column LIKE pattern;

UPDATE table_name SET column=value WHERE column LIKE pattern;

DELETE FROM table_name WHERE column LIKE pattern;

In the sql like syntax mentioned above the “pattern” is the one that is defined by the usage of wildcards.

Let’s try to understand the usage of SQL LIKE statement along with wildcards by some examples. Consider the following Customer table for the example.

1. Find customer name with name starting with ‘A’.

   SELECT CustomerName FROM Customer WHERE CustomerName LIKE 'A%';
   

   Output: Amit Annie

2. Find customer name with name ending with ‘e’.

   SELECT CustomerName FROM Customer WHERE CustomerName LIKE '%e'
   

   Output: Annie

3. Find customer name with name starting with ‘A’ and ending with ‘t’.

   SELECT CustomerName FROM Customer WHERE CustomerName LIKE 'A%t'
   

   Output: Amit

4. Find customer name with name containing ‘n’ at any position.

   SELECT CustomerName FROM Customer WHERE CustomerName LIKE '%n%'
   

   Output: Annie John

5. Find customer name with name containing ‘n’ at second position.

   SELECT CustomerName FROM Customer WHERE CustomerName LIKE '_n%'
   

   Output: Annie

6. Find customer name with name containing ‘i’ at third position and ending with ‘t’.

   SELECT CustomerName FROM Customer WHERE CustomerName LIKE '__i%t'
   

   Output: Amit

Sometimes we want to get records that doesn’t match the like pattern. In that case we can use sql not like operator. SQL not like statement syntax will be like below.

SELECT column FROM table_name WHERE column NOT LIKE pattern;

UPDATE table_name SET column=value WHERE column NOT LIKE pattern;

DELETE FROM table_name WHERE column NOT LIKE pattern;

As an example, let’s say we want the list of customer names that don’t start with ‘A’. Below query will give us the required result set.

SELECT CustomerName FROM Customer WHERE CustomerName NOT LIKE 'A%';

Output: John

We can have multiple like statements in SQL query. For example, if we want a list of customer names starting from ‘Jo’ and ‘Am’ then we will have to use multiple like statements like below.

SELECT CustomerName FROM Customer WHERE CustomerName LIKE 'Am%' OR CustomerName LIKE 'Jo%';

That’s all for SQL like operator and SQL not like operator examples.