"invalid regular expression: parentheses () not balanced"-CodePudding

I'm getting the error invalid regular expression: parentheses () not balanced while executing a query. The error refers to this part:

substring(every_x1, '\m[0-9]*\.?[0-9]'),
substring(every_x2, '\m[0-9]*\(?|-|to|TO)'),
substring(every_x2, '\m[0-9]*\(?|time|TIME)')

I checked it in an online parentheses checker, and it's supposed to be okay. What am I doing wrong?

CodePudding user response：

I dont know what is the exact pattern you are looking but if you were looking to find the actual "(" (parentheses) sign maybe you should escape it the second time also, somethin like in this example:

select substring(every_x2, '\m[0-9]*\(?|-|to|TO\)')::float as part_1

CodePudding user response：

Try this regular expression '\m(\d (?:\s*-\s*|\s*to\s*)\d )\M').

select substring('123 12-56 xyz' from '\m(\d (?:\s*-\s*|\s*to\s*)\d )\M');
-- 12-56 
select substring('123 12 to 56 xyz' from '\m(\d (?:\s*-\s*|\s*to\s*)\d )\M');
-- 12 to 56