0

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.

Improve this question
2
  • Is your third pattern 971971 supposed to be 9719715? Can it be generalised - are you really looking to replace everything up to the first 5 in the string, or keep the last 8 characters of any value for instance; or are there other values you don't want to modify? Commented Aug 11, 2016 at 14:41
  • If you have any control over it, it would be better to fix the data model. Instead of a single column for the phone number, you should have three: one for country code, one for area (region, province etc.) code and one for the actual number. Unfortunately, quite often people who post here don't have enough power to make such changes... Commented Aug 11, 2016 at 19:12

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.