Not Like Operator - (MySQL Regular Expressions)
In MySQL, the "NOT LIKE" operator is used to search for records that do not match a specified pattern using regular expressions. In this tutorial, we'll discuss how to use the "NOT LIKE" operator with regular expressions in MySQL.
Syntax
The syntax for using the "NOT LIKE" operator with regular expressions in MySQL is as follows:
SELECT column_names
FROM table_name
WHERE column_name NOT LIKE 'pattern' [ESCAPE 'escape_char'];
Here, "column_names" and "table_name" represent the columns and table to search, respectively. "column_name" represents the column to search, and "pattern" represents the regular expression pattern to match against.
The optional "ESCAPE" clause allows you to specify an escape character to use in the pattern.
Example
Let's say we have a table called "students" that looks like this:
| id | name | age |
|---|---|---|
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Charlie | 18 |
| 4 | Dave | 19 |
If we want to find all students whose names do not begin with the letter "A", we can use the "NOT LIKE" operator with the regular expression pattern "^A%". Here's how we can implement it:
SELECT *
FROM students
WHERE name NOT LIKE '^A%';
This will return all records where the "name" column does not start with the letter "A".
Output
When we run the example code above, the output will be:
| id | name | age |
|---|---|---|
| 2 | Bob | 22 |
| 3 | Charlie | 18 |
| 4 | Dave | 19 |
This is because the "NOT LIKE" operator returned all records where the "name" column does not start with the letter "A".
Explanation
In the example above, we used the "NOT LIKE" operator with the regular expression pattern "^A%" to search for all records where the "name" column does not start with the letter "A".
The "^" character signifies the start of a string, while the "%" character signifies a wildcard that matches any number of characters. By combining these two characters, we can find all records where the "name" column does not begin with the letter "A".
Use
The "NOT LIKE" operator with regular expressions in MySQL is useful for searching for records that do not match a specified pattern. This can be useful in a variety of scenarios, such as filtering out invalid data or searching for records that meet certain criteria.
Important Points
- The "NOT LIKE" operator is used to search for records that do not match a specified pattern using regular expressions.
- Regular expressions can use characters such as "^" and "%" to match patterns.
- The "ESCAPE" clause can be used to specify an escape character to use in the pattern.
Summary
In this tutorial, we discussed how to use the "NOT LIKE" operator with regular expressions in MySQL. We covered the syntax, example, output, explanation, use, and important points of the "NOT LIKE" operator. With this knowledge, you can now use regular expressions in MySQL to search for records that do not match a specified pattern.