How to replace occurrence only on the start of the string in Oracle SQL?

I have a source column and I want to search for string values starting with 05, 5 971971 and 97105 to be replaced by 9715. As showin in output table.

SOURCE OUTPUT 0514377920 971514377920 544233920 971544233920 971971511233920 971511233920 9710511233920 971511233920

I tried following which works for first case.

SELECT REGEXP_REPLACE ('0544377905', '^(\05*)', '9715')FROM dual;

But following is not working, for second case:

SELECT REGEXP_REPLACE ('544377905', '^(\5*)', '9715')FROM dual;

Something is wrong with my regular expression. As I am getting: ORA-12727: invalid back reference in regular expression.

Solution

You can provide your four patterns using alternation; that is, in parentheses with a vertical bar between them:

with t(source) as (
  select '0514377920' from dual
  union all select '544233920' from dual
  union all select '971971511233920' from dual
  union all select '9710511233920' from dual
)
SELECT source, REGEXP_REPLACE (source, '^(05|5|9719715|97105)', '9715') as output
FROM t;

SOURCE          OUTPUT             
--------------- --------------------
0514377920      971514377920        
544233920       971544233920        
971971511233920 971511233920        
9710511233920   971511233920

Depending on your data and any other restrictions you have, you may be able to make it as simple as replacing the first part of any string that has a 5 in it, which works for your small sample:

SELECT source, REGEXP_REPLACE (source, '^.[^5]?5', '9715') as output
FROM t;

That matches zero or more characters that are not 5, followed by a 5. That may be too simplistic for your real situation though.