What are the different wildcard characters which can be used with NOT LIKE operator?
As we know that NOT LIKE operator is used along with WILDCARD characters for not getting the string having specified string. Basically, WILDCARD is the characters that help search data matching complex criteria. Followings are the types of wildcard which can be used in conjunction with NOT LIKE operator:
% - The Percentage
The ‘%’ wildcard is used to specify a pattern of 0, 1 or more characters. A basic syntax for using % wildcard with NOT LIKE operator is as follows:
Select Statement…Where column_name NOT LIKE ‘X%’
Here X is any specified starting pattern such as the single character of more and % matches any number of characters starting from 0.
The percentage ‘%’ wildcard can be in many ways with the specified pattern. Followings are some examples showing different NOT LIKE operators with %. Here in these examples X represents the specified pattern:
1. NOT LIKE ‘X%’: It will find any values other than that start with “X”.
Example
mysql> Select * from Student Where Name NOT LIKE 'a%';
+------+---------+---------+-----------+--------------------+
| Id | Name | Address | Subject | year_of_Admission |
+------+---------+---------+-----------+--------------------+
| 1 | Gaurav | Delhi | Computers | 2001 |
| 15 | Harshit | Delhi | Commerce | 2009 |
| 20 | Gaurav | Jaipur | Computers | 2017 |
| 21 | Yashraj | NULL | Math | 2000 |
+------+---------+---------+-----------+--------------------+
4 rows in set (0.00 sec)
2. NOT LIKE ‘%X’: It will find any values other than that end with “X”.
Example
mysql> Select * from Student Where Name NOT LIKE '%v';
+------+---------+---------+----------+--------------------+
| Id | Name | Address | Subject | year_of_Admission |
+------+---------+---------+----------+--------------------+
| 15 | Harshit | Delhi | Commerce | 2009 |
| 21 | Yashraj | NULL | Math | 2000 |
+------+---------+---------+----------+--------------------+
2 rows in set (0.00 sec)
3. NOT LIKE ‘%X%’: It will find any values other than that have “X” in any position.
Example
mysql> Select * from Student Where Name NOT LIKE '%h%';
+------+--------+---------+-----------+--------------------+
| Id | Name | Address | Subject | year_of_Admission |
+------+--------+---------+-----------+--------------------+
| 1 | Gaurav | Delhi | Computers | 2001 |
| 2 | Aarav | Mumbai | History | 2010 |
| 20 | Gaurav | Jaipur | Computers | 2017 |
+------+--------+---------+-----------+--------------------+
3 rows in set (0.00 sec)
4. NOT LIKE ‘X%X’: It will find any values other than that start with “X” and ends with “X”.
Example
mysql> Select * from Student Where Name NOT LIKE 'a%v';
+------+---------+---------+-----------+--------------------+
| Id | Name | Address | Subject | year_of_Admission |
+------+---------+---------+-----------+--------------------+
| 1 | Gaurav | Delhi | Computers | 2001 |
| 15 | Harshit | Delhi | Commerce | 2009 |
| 20 | Gaurav | Jaipur | Computers | 2017 |
| 21 | Yashraj | NULL | Math | 2000 |
+------+---------+---------+-----------+--------------------+
4 rows in set (0.00 sec)
_ The Underscore
The underscore wildcard is used to match exactly one character. A basic syntax for using a _ wildcard with NOT LIKE operator is as follows:
Select Statement…Where column_name NOT LIKE ‘X_’
Here X is any specified starting pattern such as the single character of more and _ matches exactly one character.
The underscore ‘_’ wildcard can be used, alone or in combination with %, in many ways with the specified pattern. Followings are some examples showing different NOT LIKE operators with %. Here in these examples X represents the specified pattern:
1. NOT LIKE ‘X_’: It will find any values other than that start with “X” and having exactly one character after X.
Example
mysql> Select * from Student Where Year_of_Admission NOT LIKE '200_';
+------+--------+---------+-----------+--------------------+
| Id | Name | Address | Subject | year_of_admission |
+------+--------+---------+-----------+--------------------+
| 2 | Aarav | Mumbai | History | 2010 |
| 20 | Gaurav | Jaipur | Computers | 2017 |
+------+--------+---------+-----------+--------------------+
2 rows in set (0.00 sec)
2. NOT LIKE ‘_X’: It will find any values other than that end with “X” and having exactly one character before X.
Example
mysql> Select * from Student Where Year_of_Admission NOT LIKE '_017';
+------+---------+---------+-----------+--------------------+
| Id | Name | Address | Subject | year_of_admission |
+------+---------+---------+-----------+--------------------+
| 1 | Gaurav | Delhi | Computers | 2001 |
| 2 | Aarav | Mumbai | History | 2010 |
| 15 | Harshit | Delhi | Commerce | 2009 |
| 21 | Yashraj | NULL | Math | 2000 |
+------+---------+---------+-----------+--------------------+
4 rows in set (0.00 sec)
3. NOT LIKE _X%: It is in combination with % wildcard. It will find any values other than that have X in the second position.
Example
mysql> Select * from Student Where Name NOT LIKE '_a%';
Empty set (0.00 sec)
4. NOT LIKE X_%_%: It is in combination with % wildcard. It will find any values other than that start with X and at least three characters in length.
Example
mysql> Select * from Student Where Name NOT LIKE 'g_%_%';
+------+---------+---------+----------+--------------------+
| Id | Name | Address | Subject | year_of_Admission |
+------+---------+---------+----------+--------------------+
| 2 | Aarav | Mumbai | History | 2010 |
| 15 | Harshit | Delhi | Commerce | 2009 |
| 21 | Yashraj | NULL | Math | 2000 |
+------+---------+---------+----------+--------------------+
3 rows in set (0.00 sec)
Published on 20-Feb-2018 14:59:43
