I have text strings like the following:

g06, f03, k090

And I would like to remove all zeros that immediately follow a letter. So the output would be:

g6, f3, k90

I can do something like:

select regexp_replace('k090','0','') 

But this will replace all 0’s and not just the one immediately following a letter.

CodePudding user response：

You can use

SELECT REGEXP_REPLACE('g06, f03, k090', '([[:alpha:]])0 ', '\1', 'g')

Here,

• ([[:alpha:]]) - captures a single letter into Group 1
• 0 - matches one or more zeros.

The replacement pattern is \1, a replacement backreference to Group 1 value (so it is not removed).

See the regex demo (DB fiddle).