update records in oracle8i database where we cannot use regex

Question

I have a task in hand where I need to update phone numbers format of around 200 records. The current format is

[email protected],[email protected]

The new format should be

[email protected],[email protected]

I tried to use regex but oracle 8i doesn't allow me to use it.

Answer 1

This looks like a really bad data format -- storing multiple values in a string. But, you can use replace(), at least for the example you have given:

select replace(col, ',0', ',+61')
from t;

I would check that this does what you want. In the end, the query might look like:

update t
    set col = replace(col, ',0', ',+61')
    where col like '%,0';