Dev G Dev G - 3 months ago 33
SQL Question

Oracle: Replace first character to other character

I have a table in oracle in which we have one column having data as

B12345
, means first alphabet always
B
and followed by numeric. I want to replace all such instances with
BH
that will become
BH12345


So if already there is a value called BH45678 in that column don't update.

Only where find B followed by numeric need updates.

vkp vkp
Answer

Get the rows which have B followed by digits using regexp_like. Then use replace to replace B with BH for those rows.

select replace(col,'B','BH')
from tablename
where regexp_like(col,'^B\d+$')