how to replace substring at the end of string using SQL?

Question

I have data in a table like this:

xxx-xxxxxxx-29
xxx-xxxxxx-87
xxx-xxxxxxxxx-2
xxx-xxx-33

I need to replace every -n at the end with -0n (to add a zero between the dash and the number). Leave -nn as it is.

So, if 2nd character from the right is '-' replace it with '-0'

can this be done using UPDATE?

Answer 1

I need to replace every -n at the end with -0n (to add a zero between the dash and the number).

One option is to use regexp_replace():

update mytable set mycol = regexp_replace(mycol, '-(\d)$', '-0\1');

'-(\d)$' matches on a dash followed by a one digit at the end of the string, and captures the digit; you can then prepend a leading '0'.