How to remove specific initial characters from a column in oracle?-CodePudding

I have column by the name of phone_number and it consists different types of numbers for example:

phone_number
078912354
93784385483
009378248448
776868886

So I want to remove all the initial numbers which starts with(0,93,0093). The expected result which I want is:

phone_number
78912354
784385483
78248448
776868886

CodePudding user response：

Here's one option:

Sample data:

SQL> select * from test order by phone_number;

PHONE_NUMBER NEW_PHONE_NUMBER
------------ --------------------
009378248448
078912354
776868886
93784385483

Remove leading characters you mentioned:

SQL> update test set
  2    new_phone_number = regexp_replace(phone_number, '^(0093|093|93|0)');

4 rows updated.

Result:

SQL> select * from test order by phone_number;

PHONE_NUMBER NEW_PHONE_NUMBER
------------ --------------------
009378248448 78248448
078912354    78912354
776868886    776868886
93784385483  784385483

SQL>