MySQL RegEx: Got error 'empty (sub)expression' from regexp

Question

I have a column participants which contains a value like "99005|99001|99002|99001999|99004" which are user logins.

What exactly I want is to match "99001" without matching "99001999".

Here is my method:

SELECT * FROM `bv_sklad_products` WHERE `stage`=4 AND `participants` REGEXP ('^([^\|]+(\|))*(99001|99005)((\|)[^\|]+)*$') AND `start_date` BETWEEN '2015-07-09' AND '2015-07-10' ORDER BY `id` DESC LIMIT 0,100

And the error message I get:

Got error 'empty (sub)expression' from regexp

What am I doing wrong?

I guess you can do it with `[[:<:]](99001|99005)[[:>:]]` if you need to find both `99001` and `99005`. — Wiktor Stribiżew, Jul 10 '15 at 08:48
the real reason why it didn't work is because you need a double backslash before the pipe `\\|` — santiago arizti, Sep 04 '18 at 22:10

score 6 · Answer 1 · answered Jul 10 '15 at 08:47

6

You may use word boundaries.

regexp  '[[:<:]]99001[[:>:]]'

or

regexp '(^|[|])99001([|]|$)'

answered Jul 10 '15 at 08:47

Avinash Raj

172,303
28
230
274

1

Thanks. It works perfectly! But how does the top one works? – BorissOliSiin Jul 10 '15 at 08:54
[Here is an answer to your question](http://stackoverflow.com/a/18902555/3832970). – Wiktor Stribiżew Jul 10 '15 at 08:58

MySQL RegEx: Got error 'empty (sub)expression' from regexp

1 Answers1

Linked