MySQL insert parameter inside REGEXP

Question

I use MySQL C Connector. My prepared statement looks like this:

"SELECT x FROM y WHERE z REGEXP '^BLA?$'"

The problem is that ? character is treated like part of REGEXP, when I need to insert parameter there later. How can I make ? to be treated like placeholder for data?

What type of input are you accepting into the `?` parameter? Is it strictly range-limited and validated? There may be better solutions than concatenating it into the regexp. — Michael Berkowski, Sep 29 '15 at 19:41
@MichaelBerkowski wasn't sure; I almost never need to use regexp in MySQL, so that aspect is mostly "academic" for me. — Uueerdo, Sep 29 '15 at 19:42

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

REGEXP CONCAT('^BLA', ?, '$') this should work. However, if you need that parameter AND a '?', like this CONCAT('^BLAH', ?, '?$'), I am not sure if that latter ? will need escaped, but according to this it should be fine without escaping it.

edited May 23 '17 at 11:44

Community

1
1

answered Sep 29 '15 at 19:52

Uueerdo

15,723
1
16
21

as a note, this is applicable for pretty much ANY situation where you need to embed a placeholder some other string. `'?'` is a question mark to mysql, so you need to "extract" the `?` from the string, which is where concat() comes in handy. – Marc B Sep 29 '15 at 19:57

MySQL insert parameter inside REGEXP

1 Answers1