Delete a specific character from a query output

I am using regexp_replace in my SELECT statement to remove a character "0" from a query output. For example, a string ABC012 should become ABC12, or XYZ045 becomes XYZ45.

I tried regexp_replace(test_column, '0.[0-9]{2}') with the aim to delete the "0" in the middle without affecting the other strings around but the output is still the same. I'll be honest that my regexp knowledge is not really good, if you can share some pointers, that'll be appreciated.

Solution

We can try making the replacement with the help of capture groups:

SELECT test_column,
       REGEXP_REPLACE(test_column, '^([A-Z]+)0+([0-9]+)$', '\\1\\2') AS test_column_out
FROM yourTable;

The regex pattern here says to match:

^ from the start of the value
([A-Z]+) match and capture in \1 the leading alpha portion
0+ match one or more leading zeroes in the numeric portion
([0-9]+) then match and capture in \2 the remainder of the numeric portion $ end of the value

By replacing with \1\2 we are splicing out the leading zeroes, should they occur.