0

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 - : ( ) & . , ? ! ' | < >.

Improve this question
10
  • 1
    And your question is? Commented Jan 27, 2016 at 13:08
  • 1
    So just add them to your regular expression. You already have half of them. Some of them you'll need to escape obviously. Commented Jan 27, 2016 at 13:08
  • Try SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9 :()&.,?-]+$'. Yours also returns records with !, and ", right? Commented Jan 27, 2016 at 13:13
  • Yes i need all of them Commented Jan 27, 2016 at 13:16
  • Then you must specify which symbols you need to add to the regex pattern. We cannot guess which of them must be there. Note also that Unicode symbols cannot be used with MySQL regexp. Otherwise, no one will be able to answer the question but you. Commented Jan 27, 2016 at 13:19

3 Answers 3

Reset to default
1

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 :()&.,?!''|<>-]+$'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

3 Comments

Oh, accepted. I do not think (like the - in there has become a \- already is really that helpful.
It was with the question as it was at the time I wrote ... but the question was edited in the mean time. Hence my earlier comment about the question itself being a moving target... Sorry.
- has special meaning inside regexps -- namely to connote a 'range' of characters, such as A-Z. However, \- is turned into - before checking for that special meaning. Hence '[0-9 \-\:] looks for a digit or any character between space and colon, identical to '[0-9 -:]. The way to get - to be recognized as just the dash character is to put it first (or last) inside the brackets.
0

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.

Improve this answer

1 Comment

@swa66 - There are two 'nots', one before regexp, one just inside [^...], so it is equivalent.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.