SQL Match Special Characters Regexp

Question

I'm looking for an SQL statement that will return only rows of my table whose Name field contains special characters (excluding underscores).

I've tried:

SELECT * FROM 'table' WHERE Name REGEXP '^[!#$%&()*+,\-./:;<=>?@[\\\]^`{|}~]+$'

But no dice, this returns an empty result set (despite there being rows I specifically added with Name fields containing %, $, and # characters).

Answer 1

The first problem seems to be is the ^ and $ signs (Mike C summarized it quicker than I did why...)

But I see escaping problems too: all special characters that mean something in regexp should be escaped specially placed in the [], so [, ], ^, -

Here is a question about how to escape special characters inside character groups in MySQL regexes.

Conclusion detailed in the regex documentation:

A bracket expression is a list of characters enclosed in '[]'. It normally matches any single character from the list (but see below).

• If the list begins with '^', it matches any single character (but see below) not from the rest of the list.

• If two characters in the list are separated by '-', this is shorthand for the full range of characters between those two (inclusive) in the collating sequence, e.g. '[0-9]' in ASCII matches any decimal digit.

• It is illegal(!) for two ranges to share an endpoint, e.g. 'a-c-e'. Ranges are very collating sequence-dependent, and portable programs should avoid relying on them.

• To include a literal ']' in the list, make it the first character (following a possible '^').

• To include a literal '-', make it the first orlast character, or the second endpoint of a range.

• To use a literal '-' as the first endpoint of a range, enclose it in '[.' and '.]' to make it a collating element (see below).

With the exception of these and some combinations using '[' (see next paragraphs), all other special characters, including '\', lose their special significance within a bracket expression.

EDIT Here is an SQL fiddle about some interesting regexes regarding the ] character

DDL: create table txt ( txt varchar(200) );

insert into txt values ('ab[]cde');
insert into txt values ('ab[cde');
insert into txt values ('ab]cde');
insert into txt values ('ab[]]]]cde');
insert into txt values ('ab[[[[]cde');
insert into txt values ('ab\\]]]]cde');
insert into txt values ('ab[wut?wut?]cde');

Queries:

Naive approach to match a group of [ and ] chars. Syntactically OK, but the group is the single [ char, and it matches multiple ] chars afterwards.

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[]]+cde';

Escaped -> same ???

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[\]]+cde';

Double escape -> doesn't work, group is now a [ and a \

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[\\]]+cde';

Swapping the closing bracket with the opening one inside the group. This is the weirdest regex I ever wrote - to this point...

SELECT * FROM txt WHERE txt 
REGEXP 'ab[][]+cde';

I will get killed by such a (totally valid!) regex in a weird nightmare, I think:

SELECT * FROM txt WHERE txt 
REGEXP 'ab[]wut?[]+cde';

Answer 2

This regex should match names that ONLY contain special characters. You specify the carat (^) which signifies the start of the string, your character class with your list of special characters, the plus sign (+) to indicate one or more, and then the dollar to signify the end of the string. You need to account for non-special character in the string. You could try something like this:

WHERE Name REGEXP '^.*?[!#$%&()*+,\-./:;<=>?@[\\\]^`{|}~]+.*?$'

I added the .*? at the beginning and end to allow for non-special characters before and after the special character. BTW, you probably don't need the (+) any more, since one special would be enough for a match.