Escaping question mark in mySQL regex

Question

I have this simple query that uses regex. I don't know why it returns an error every time I try to run it.

select *
from mytable
where thumbnail_url regexp '^\?.+'

Basically, I want to see if any string in this list starts with a question mark or not. I am connected to Amazon RDS through MySQLWorkbench 6.3.

Error Code: 1139. Got error 'repetition-operator operand invalid' from regexp — Mojtaba Komeili
– Mojtaba Komeili, Commented Mar 17, 2018 at 1:01
Double the backslash. MySQL requires the first one to escape the second backslash which so it can be treated as a literal character in the regex (to modify/escape ?) rather than as an escaping character within the SQL itself. Thus use regexp '^\\?.+' — Michael Berkowski
– Michael Berkowski, Commented Mar 17, 2018 at 1:03

Michael Berkowski · Accepted Answer · 2018-03-17 01:09:51Z

10

When you use a backslash to escape a regexp character in MySQL, you must also escape the backslash itself as \\. MySQL would see a single \ character and attempt to use it as an escaping character within the SQL statement itself rather than treating it as a literal. But you need it to act as a literal in order to modify the ? within the regex.

The first \ escapes the second \, resulting in a literal regex sequence \? to represent a single literal ? in the matching string.

MySQL documentation on string literals...

Format your expression with an escaped backslash as:

select *
from mytable
where thumbnail_url regexp '^\\?.+'

answered Mar 17, 2018 at 1:09

Michael Berkowski

271k47 gold badges451 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Escaping question mark in mySQL regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related