CODEWITHSUNDEEP
sqloracle-database
Rifqi Fitriady
Rifqi Fitriady

Reputation: 93

SQL change value in varchar2 datatype using SQL script

i have a problem here. in my database i've value in mobile_phone column like 628932323 but i wanna change every row which use 62 to 0

old value : 628932323
new value : 08932323

can someone help me to solve my problems. thanks

Upvotes: 0

Views: 108

Answers (1)

Colin 't Hart
Colin 't Hart

Reputation: 7729

If you are using oracle 10g or higher, you can use the regexp_replace function http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

select regexp_replace(your_column, '62([:digit:]*)', '0\2')
from your_table;

Or a more old-fashioned way without:

select case
         when your_column like '62%' then '0' || substr(your_column, 3)
       else
         your_column
       end
from your_table;

Just be careful with leading spaces or other characters in your phone number strings.

Upvotes: 1

Related Questions