T-SQL - NOT LIKE Operator

The T-SQL (Transact-SQL) NOT LIKE operator is the negation of LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the LIKE or NOT LIKE operator are given below:

Wildcard Characters in T-SQL

Symbol	Description	Example
%	Represents zero, one or multiple characters.	'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_	Represents one character.	'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
[]	Represents any single character specified within [].	'[ack]%' represents a value that starts with a, c or k, for example - ant, cat or kite etc.
-	Represents a range of characters.	'[a-d]%' represents a value that starts with a, b, c or d, for example - ant, bat, cat or dog etc.
[^]	Represents any character not specified within [].	'[^ack]%' represents a value that does not start with a, c or k, for example - bat, dog or rat etc.

Syntax

The syntax for using LIKE and NOT LIKE operators in T-SQL (Transact-SQL) are given below:

/* Using LIKE operator */
SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

/* Using NOT LIKE operator */
SELECT column1, column2, ...
FROM table_name
WHERE column NOT LIKE pattern;

The table below describes patterns which is used with LIKE operator and uses (%) and (_).

Pattern	Description
'J%'	A value that start with "J".
'%n'	A value that end with "n".
'%oh%'	A value that have "oh" in any position.
'_o%'	A value that have "o" in the second position.
'J_%'	A value that start with "J" and have at least 2 characters.
'J__%'	A value that start with "J" and have at least 3 characters.
'J%n'	A value that start with "J" and ends with "n".

Example:

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

Using the % Wildcard : To select all records of the Employee table with Name starting with 'Jo', the query is given below.
```
SELECT * FROM Employee
WHERE Name LIKE 'Jo%';
```
This will produce the result as shown below:

EmpID Name City Age Salary

1 John London 25 3000

3 Jo Paris 27 2800
Using the % Wildcard with NOT LIKE operator: NOT LIKE operator is used as the negation of LIKE operator. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:
```
SELECT * FROM Employee
WHERE Name NOT LIKE 'Jo%';
```
This will produce the result as shown below:

EmpID Name City Age Salary

2 Marry New York 24 2750

4 Kim Amsterdam 30 3100

5 Ramesh New Delhi 28 3000

6 Huang Beijing 28 2800
Using the _ Wildcard : To select all records of the Employee table with Name containing 'o' as second character, the query is mentioned below.
```
SELECT * FROM Employee
WHERE Name LIKE '_o%';
```
The result of the above code will be:

EmpID Name City Age Salary

1 John London 25 3000

3 Jo Paris 27 2800
Using the _ Wildcard with NOT LIKE operator: To select all records of the Employee table with Name not containing 'o' as second character, the query is given below:.
```
SELECT * FROM Employee
WHERE Name NOT LIKE '_o%';
```
The result of the above code will be:

EmpID Name City Age Salary

2 Marry New York 24 2750

4 Kim Amsterdam 30 3100

5 Ramesh New Delhi 28 3000

6 Huang Beijing 28 2800

EmpID	Name	City	Age	Salary
2	Marry	New York	24	2750
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

EmpID	Name	City	Age	Salary
2	Marry	New York	24	2750
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

❮ T-SQL - Operators