Regular expression REGEXP_REPLACE not supporting POSIX ERE

I have a case to replace a period when its surrounded by Alphabets and not when surrounded by Numbers. I figured out Regular Expression that can do this but the pattern is not working in SQL

SELECT REGEXP_REPLACE(“Amount.fee:0.75,Amount.tot:645.55”,"(?<!\d)(.)(?!\d)","_",“ig”);

Expected output: Amount_fee:0.75,Amount_tot:645.55

Note, I am trying this because I couldn’t access JSON key as it has period in it.

Also verified the pattern “(?<!\d)(.)(?!\d)” using https://coding.tools/regex-replace and it working fine. But, SQL is not working. Am using 7.1.9 and POSIX Enhanced Regular expression are supposed to be work. Would like some help here.

Can you put in the exact SQL you ran, and be careful that the quotes don’t get replaced by slanted quotes?

I get this

SELECT REGEXP_REPLACE("Amount.fee:0.75,Amount.tot:645.55","(?<!\d)(.)(?!\d)","_","ig");
ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp

Also, it seems your pattern is matching non-digits, not specifically alphabetic chars.

I didn’t get your expected result with your pattern in https://coding.tools/regex-replace

1 Like