4

I tried running this query:

SELECT column FROM table WHERE column REGEXP '[^A-Za-z\-\']'

but this returns

#1139 - Got error 'invalid character range' from regexp

which seems to me like the - in the character class is not being escaped, and instead read as an invalid range. Is there some other way that it's suppose to be escaped for mysql to be the literal -?

This regex works as expected outside of mysql, https://regex101.com/r/wE8vY5/1.

I came up with an alternative to that regex which is

SELECT column FROM table WHERE column NOT REGEXP '([:alpha:]|-|\')'

so the question isn't how do I get this to work. The question is why doesn't the first regex work?

Here's a SQL fiddle of the issue, http://sqlfiddle.com/#!9/f8a006/1.

Also, there is no language being used here, query is being run at DB level.

Regex in PHP: http://sandbox.onlinephpfunctions.com/code/10f5fe2939bdbbbebcc986c171a97c0d63d06e55

Regex in JS: https://jsfiddle.net/6ay4zmrb/

Improve this question
1
  • This actually works in MySQL 8 but not in 5.7 Commented Apr 23, 2021 at 0:13

2 Answers 2

Reset to default
10

Just change the order.

SELECT column FROM table WHERE column REGEXP '[^-A-Za-z\']'
Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

Yup, changing the order works. Is the escaping not supported though?
What tool did you use? If that string is in PHP, then you need 2 levels of escaping, one for PHP, a second one for SQL.
@RickJames I'm running queries in phpmyadmin directly, no intermediary. I've also tried the double escaping, it also didn't work. sqlfiddle.com/#!9/f8a006/1
It is interesting that changing the order works and that the - doesn't need to be escaped at all.
The - is a special case since it is used in the syntax for a 'range'. By putting it first, it is clearly not syntax for a range, so it can be treated as an ordinary character. Similarly, some of these: ^$+* change there meaning depending on context.
|
3

@Avinash Raj is correct the - must be first (or last). The \ is not an escape character in POSIX, which is what mysql uses, https://dev.mysql.com/doc/refman/5.1/en/regexp.html.

One key syntactic difference is that the backslash is NOT a metacharacter in a POSIX bracket expression.

-http://www.regular-expressions.info/posixbrackets.html

What special characters must be escaped in regular expressions?

Inside character classes, the backslash is a literal character in POSIX regular expressions. You cannot use it to escape anything. You have to use "clever placement" if you want to include character class metacharacters as literals. Put the ^ anywhere except at the start, the ] at the start, and the - at the start or the end of the character class to match these literally

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.