CODEWITHSUNDEEP
mysqlregex
motioz
motioz

Reputation: 662

Regex that contain only alphanumeric and special characters in MySQL

I'm trying to select all rows that contain only english and special characters in MySQL using:

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9 \-\:\(\)\&\.\,\?]+$'

I don't know what is wrong when I try adding - : ( ) & . , ? ! ' | < >.

Upvotes: 0

Views: 7521

Answers (3)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626748

The special character i want: - : ( ) & . , ? ! ' | < >

Note that when you add characters to a character class, you do not have to blindly escape all of them, and if you have to escape them, you must use a double \\ backslash in MySQL regex. However, it is not necessary. When you place the - hyphen at the start or end of the character class, or right after a POSIX character class, it is treated as a literal hyphen. Other "special" characters you need do not have to be escaped in the character class.

Also, ' should be doubled in a single quoted string literal.

So, use

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9 :()&.,?!''|<>-]+$'

Upvotes: 1

user3277192
user3277192

Reputation:

As some have commented already all you need to do is add the additional characters to your regexp between the [ ].

Some of them will need to be escaped with a \ in front of them (like the - in there has become a \- already.

To learn the most out of it: add them one by one.

Ref: http://dev.mysql.com/doc/refman/5.5/en/regexp.html

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can use not regexp:

select *
from table
where column not regexp '[^A-Za-z0-9:()&.,?!''|<>]';

That is, return rows where column has a character that is not in your list.

Upvotes: 0

Related Questions