Replace Characters from result Oracle SQL developer

Question

I have been trying to sort out my problem in getting my MOBILE NOs, I searched every where to replace some characters from my column MOBILE. I have tried regexp_replace(mobile, '03', '+923') as MOBILE and replace(mobile, '03', '+923') as MOBILE in ORACLE SQL DEVELOPER.I want to replace these number on the start in the column, But I have been facing the problem which is that some of my column records have these characters in the middle of column, which are also being replaced with this query. My Query goes like this:

select replace(mobile, '03', '+923') as MOBILE
from detail_accounts

Result i get is:

MOBILE
--------------
+9230+923182802
+9230+923159381
+9230+923135716
+9230+923120242
+9230+923113709
+9230+923113707
+9230+923112794
+9230+923082646
+9230+923061622
+9230+923+923+92357
+9230+923023594
+9230+923016147
+9230+923015115
+9230+923010297
+9230+9230+923075
--------------

and result of original columns without replace function is:

MOBILE
--------------
03003182802
03003159381
03003135716
03003120242
03003113709
03003113707
03003112794
03003082646
03003061622
03003030357
03003023594
03003016147
03003015115
03003010297
03003003075
--------------

Please ask me anything if there is some confusion. I tried my best to get the relevant question on StackOverflow, Pardon me If I have asked a already answered question. Thank you!

Answer 1

You can try the following, The pattern '^03' would look for strings which match the start of the string, and then replace it with '+923'

Eg:

select '03003182802' as orig_num,regexp_replace('03003182802','^03','+923') as replaced_num
  from dual

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=d81d09298cc499d5c30f200d45b20c15

Answer 2

You can use regex_replace

select regexp_replace('03003182802','03','+923',1,1) from dual

Output

+923003182802

CHECK DEMO HERE

For your query

 select regexp_replace(mobile,'03','+923',1,1) as MOBILE
 from detail_accounts