CODEWITHSUNDEEP
mysqlregexdatabaseregex-negation
user9371654
user9371654

Reputation: 2398

How to negate square brackets in MySQL regex

I am trying to match a variable name that does not contain square brackets using MySQL regexp. However when I write something includes:

[^[]]

MySQL does not execute the matching and returns OK in the Message panel, which mean the query has not been executed.

Question: How to negate square brackets in MySQL?

EDIT:

I use MysQL server 8.0.16 and MySQL workbench 8.0

Upvotes: 1

Views: 2151

Answers (2)

Nick
Nick

Reputation: 147216

MySQL5.x only allows ] in a character class if it is the first character in the class (see the manual). So you need to rework your character class as [^][]. For example:

CREATE TABLE vars (varname VARCHAR(20));
INSERT INTO vars VALUES
('hello world'),
('hello [world'),
('hello world]'),
('hello [world]');
SELECT varname, varname RLIKE '^[^][]+$' AS `match`
FROM vars

Output:

varname         match
hello world     1
hello [world    0
hello world]    0
hello [world]   0

Demo on dbfiddle

MySQL 8+ uses a different regex engine (thanks @WiktorStribiżew), so the regex needs to be modified to [^]\\[] in that case (without escaping the [, MySQL complains about an "unclosed bracket expression"). This form of the regex also works fine in MySQL 5.7.

Demo on dbfiddle

Upvotes: 3

Hamed Ghasempour
Hamed Ghasempour

Reputation: 445

Use NOT in your query:

Look at this:

SELECT * FROM test WHERE name NOT REGEXP  "[\\[\\]]"

What this part of Query

REGEXP  "[\\[\\]]"

is saying, select all rows which contain [ and ].

But, using not, actually you are saying select all rows which not contain [ or ];

Upvotes: 1

Related Questions