MySQL NOT REGEXP Function

The MySQL NOT REGEXP function is the negation of REGEXP function.

The NOT REGEXP function is a synonym for the NOT RLIKE function.

Syntax

expression NOT REGEXP pattern

Parameters

`expression`	`Required.` Specify a character expression such as a column or field.
`pattern`	`Required.` Specify the regular expression matching information. See the table below for pattern syntax.

Regular Expression Patterns

Symbol	Description
^	Matches the beginning of a string.
$	Matches the end of a string.
*	Matches zero or more occurrences.
+	Matches one or more occurrences.
?	Matches zero or one occurrence.
.	Matches any character except NULL.
\|	Used like an "OR" to specify more than one alternative.
[ ]	Matches any single character specified within [].
[^ ]	Matches any single character that is not specified within [].
-	Represents a range of characters.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{m,n}	Matches at least m times, but no more than n times.
\n	n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
\d	Matches a digit character.
\D	Matches a nondigit character.
\w	Matches a word character.
\W	Matches a nonword character.
\s	Matches a whitespace character.
\S	matches a non-whitespace character.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{n,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, but not more than m times.
[..]	Matches one collation element that can be more than one character.
[=character_class=]	Represents an equivalence class. It matches all characters with the same collation value, including itself.
[:character_class:]	Represents a character class that matches all characters belonging to that class.

List of standard class name

Character Class Name	Meaning
alnum	Alphanumeric characters
alpha	Alphabetic characters
blank	Whitespace characters
cntrl	Control characters
digit	Digit characters
graph	Graphic characters
lower	Lowercase alphabetic characters
print	Graphic or space characters
punct	Punctuation characters
space	Space, tab, newline, and carriage return
upper	Uppercase alphabetic characters
xdigit	Hexadecimal digit characters

Example: Match on more than one alternative

Consider a table called Employee. The below query is used to fetch all records from this table where Name does NOT contain either "Ja", "Je" or "Ji". To specify more than one alternative for the second character of the pattern, | is used.

SELECT * FROM Employee
WHERE Name NOT REGEXP 'J(a|e|i)';

Example: Match on Beginning

Consider a table called Employee. The below query is used to fetch all records from this table where Name does NOT start with "J". Here, ^ is used to specify starting character of the pattern.

SELECT * FROM Employee
WHERE Name NOT REGEXP '^J';

Example: Match on End

Consider a table called Employee. The below query is used to fetch all records from this table where Name does NOT end with "n". Here, $ is used to specify end character of the pattern.

SELECT * FROM Employee
WHERE Name NOT REGEXP 'n$';

Example: Non-Matching List

Consider a table called Employee. The below query is used to fetch all records from this table where Name does NOT contain either "Ja", "Jb", "Jc" or "Jd". To specify the second character of the pattern, [ ] is used.

SELECT * FROM Employee
WHERE Name NOT REGEXP 'J[a-d]';

Example: Not-Matching Character Class

Consider a table called Employee. The below query is used to fetch all records from this table where Address does NOT contain any punctuation character.

SELECT * FROM Employee
WHERE Address NOT REGEXP '[[:punct:]]';

Example: Escaping Characters

To escape special character, it should be preceded with two backslashes \\. For example: the below query can be used to fetch all records from Employee table where Address does NOT contain a ( character.

SELECT * FROM Employee
WHERE Address NOT REGEXP '\\(';

❮ MySQL Functions