I'm using the below query to replace the value 2 with 5. My input string will be in the format as shown below. Each value will be delimited with carrot(^) symbol. It's working fine when there is no duplicate value. But with duplicate values it's not working. Please advice.
select regexp_replace('1^2^2222^2','(^|\^)2(\^|$)','\15\2') OUTPUT from dual;
Output:
1^5^2222^5 ( Working Fine as there is no consecutive duplicates at the starting or at the end)
.
select regexp_replace('2^2^2222^2^2','(^|\^)2(\^|$)','\15\2') OUTPUT from dual;
Output:
5^2^^5^2222^5^2(Not working as there is consecutive duplicate at the starting/end)
Please let me know how to correct this?
As others have said, the problem is the terminating delimiter caret being consumed matching the first occurrence, so it isn't seen as the opening delimiter for the next instance.
If you don't want to use nested regex calls, you could use a simple replace to double up the delimiters, then strip them afterwards:
replace(
regexp_replace(
replace(<value>, '^', '^^'), '(^|\^)2(\^|$)','\15\2'), '^^', '^')
The inner replace turns your value into 2^^2^^2222^^2^^2, so after the first occurrence is matched there is still a caret to act as the opening delimiter for the second instance, etc. The outer replace just strips those doubled-up delimiters back to single ones.
With some sample strings:
with t (input) as (
select '1^2^2222^2' from dual
union all select '2^2^2222^2^2' from dual
union all select '2^2^2222^2^^2^2' from dual
)
select input,
replace(
regexp_replace(
replace(input, '^', '^^'), '(^|\^)2(\^|$)','\15\2'), '^^', '^') as output
from t;
INPUT OUTPUT
--------------- --------------------
1^2^2222^2 1^5^2222^5
2^2^2222^2^2 5^5^2222^5^5
2^2^2222^2^^2^2 5^5^2222^5^^5^5