Regular expression REGEXP_REPLACE not supporting POSIX ERE

Help Query Tuning
1

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.

2

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