- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Questions & Answers
- What are the different wildcard characters that can be used with MySQL LIKE operator?
- What are the different wildcard characters that can be used with MySQL RLIKE operator?
- How wildcard characters can be used with MySQL CONCAT() function?
- What are the different unit values that can be used with MySQL INTERVAL keyword?
- What are the curries in which cinnamon can be used to improve the taste?
- What is the use of MySQL NOT LIKE operator?
- Which equals operator (== vs ===) should be used in JavaScript?
- What are the different time format characters used by MySQL DATE_FORMAT() function?
- Which equals operator (== vs ===) should be used in JavaScript comparisons
- What are different date format characters used by MySQL DATE_FORMAT() function?
- Which library can be used to interact with database in JSP?
- Which MySQL query can be used with the help of which we can see the list of MySQL databases?
- What are the different ways the transaction can be executed(DBMS)?
- What are the symptoms of bird flu which should not be ignored?
- How can I use 'Not Like' operator in MongoDB?