Syntax error or access violation: 1139 Got error 'empty (sub)expression' from regexp

Question

I have this regex:

REGEXP '(^0+|0+)17198671(0+|$)$'

which needs to match strings like these

US00171986710
00171986710000000000000
001719867100000000

basically what i need is that if the string has all the same numbers in it replacing the zeroes and ending with zeroes or nothing, I want to match them. I don't understand why this valid regex doesn't work with MySQL

Answer 1

The MySQL regex engine does not allow empty alternatives, like in ab(c|) or a|. The (0+|$) part matches either 1+ zeros or an empty string at the input string end, and this causes an error.

Note that (^0+|0+) means that one or more zeros do not have to appear at the start of the string, hence all you need is

REGEXP '0+171986710*$'

Details

• 0+ - one or more 0s
• 17198671 - a literal substring
• 0* - zero or more 0s
• $ - end of string.