Search code examples
oracle-databaseoracle19c

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

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
Solution

  • 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>