What is NOT LIKE operator in SQL?
The NOT LIKE operator in SQL is used on a column which is of the varchar type. Usually, it’s used with %, which is used to represent any string value, including the null character \0.
The string we pass on to this operator is not case-sensitive.
This operator can be used for several purposes, as follows:
1. Checking a substring
We can use this operator to extract those rows that don’t have a particular substring. As an example, suppose that we have a Student table, as follows:
| ID | FirstName |
|---|---|
| 1 | Alex |
| 2 | Bran |
| 3 | Chad |
The following query returns the rows where FirstName doesn’t contain the letter B:
SELECT * FROM StudentWHERE FirstName NOT LIKE '%B%'
2. Checking the start or end of a string
Another common use of this operator is to exclude those rows in which a string starts or ends with a particular string.
The following query returns the rows in which FirstName doesn’t start with A:
SELECT * FROM StudentWHERE FirstName NOT LIKE 'A%'
Similarly, the query below returns the rows in which FirstName doesn’t end with d:
SELECT * FROM StudentWHERE FirstName NOT LIKE '%d'
RELATED TAGS
