Lookahead in Oracle Regular Expressions

Question

I have a problem with Oracle regexes. I have many phone numbers in different tables. Now my task is to combine them. Therefore, I take all the spaces, underscores, cons and much more. But then the difficult part appears, which at first seemed so easy.

There are numbers both with the international code and without it, for example, 0046812345678 and 0812345678. Therefore, I want to replace one single (!) Zero with the number '0046'. I thought ^0(?=[1-9]) would do the job, but Oracle seems to think that looks are worthless. (^0)(1|2|3|4|5|6|7|8|9) does not do this job (or (^01|02|03|04|05|06|07|08|09) , for that matter), since it will replace the first nonzero number, and also make 0812345678 at 004612345678 (so the first "8" disappears).

I have been searching and trying for quite some time and cannot come up with any more options. Any help would be greatly appreciated. Thanks in advance!

Answer 1

You need to add the first result 1-9 to the result so that only numbers starting with one 0 match. To save the first 1-9, we fix it (using brackets) and add it to the replacement part (using \1 ). This seems to work:

 select regexp_replace('0812345678', '^0([1-9])', '0046\1') from dual; 

Result: 0046812345678