MySQL not like Operator

MySQL not like Operator for beginners and professionals on mysql tutorial, examples, functions, programming, mysql, literals, cursor, procedure, trigger, regexp_like(), regexp_replace operator, regular expression, regexp_instr(), crud etc.

<< Back to MYSQL

next → ← prev

MySQL NOT LIKE Operator

MySQL provides a way to find both types of records, either matched or not, with a specific value. This feature is advantageous when we want to find records that need to be edited or reported on a spreadsheet.

NOT LIKE operator in MySQL is used for pattern matching. It compares the column by the given value and returns the result that does not match the value in the NOT LIKE clause. If the statement finds the match, it will return 0. Otherwise, it will return 1. We can use this operator to perform the negation of the LIKE operator. The main advantage of this statement is to search for a range of values or values that match a pattern with the use of wildcards characters.

Syntax

The following is a basic syntax to use this operator in MySQL:

expression NOT LIKE pattern [ESCAPE 'escape_char']

In this syntax, the expression is an input string on which we will perform searching for matching the regular expression. And pattern represents the regular expression for which we are testing the string. The optional ESCAPE clause is used to specify an escape character. If we do not specify this clause, by default, it is \.

The above operator is equivalent to the below syntax:

NOT (expression LIKE pattern [ESCAPE 'escape_char'])

This syntax is generally used with the SELECT statements as follows:

SELECT name FROM table_name WHERE column_name NOT LIKE 'pattern';

Let us understand how this operator works in MySQL through examples.

Example

The below statement is the most basic example of a NOT LIKE operator. Here we have just used a string and compare if any part of the input string matches the pattern to get the result.

mysql> SELECT 
'TheDeveloperBlog' LIKE 'Java%' AS 'Match',
'TheDeveloperBlog' NOT LIKE 'Java%' AS 'Not-Match';

Here is the result:

If we want to escape any of the wildcard characters (_ and %), we need to use the backslash character (\). See the below statement where will do a search with and without the escape character:

SELECT 
  'usr+123' NOT LIKE 'usr_123' AS 'Without escape',
  'usr+123' NOT LIKE 'usr\_123' AS 'With escape';

Here is the result:

Suppose we have a table named student_info that contains the following data. We will demonstrate various examples based on this table data.

If we want to get the students detail whose name does not start with the 'A' letter, we can use the statement as follows:

mysql> SELECT * FROM student_info 
WHERE stud_name 
NOT LIKE 'A%';

Executing the statement, we will get the desired result. See the below output:

The below MySQL statement returns those rows from the table student_info that have the subject name like the pattern specified with the LIKE operator:

mysql> SELECT stud_name, subject, marks FROM student_info          
WHERE subject NOT LIKE 'M_t_s';

We will get the below output where we can see the statement does not return the records that contain the above patterns.

If we want to get the student detail whose subject name does not end with 's' character, we can use the below statement to do this:

mysql> SELECT stud_name, subject, marks FROM student_info          
WHERE subject NOT LIKE '%s';

After executing this statement, we will get the desired result:

The below example shows how to use the NOT LIKE operator with the numeric expressions:

mysql> SELECT 
  5678 NOT LIKE '56%' AS Result1,
  5678 NOT LIKE '56_' AS Result2;

Here is the result:

Next TopicMySQL Not regexp Operator

← prev next →

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

MySQL not like Operator

MySQL NOT LIKE Operator

Syntax

Example

Related Links:

.Net

Java

Related Links