Oracle - regexp_replace with nulls in a comma separated Strings

user_13721905 0
Viewed 462

I have a scenario where I have to replace the value in comma separated string based on the position. Below are the cases:

Case 1:

select regexp_replace('1,2,3','[^,]+',5,1,3)
from dual;

Returns correct result: 1,2,5

Case 2:

select regexp_replace('1,,3','[^,]+',5,1,3)
from dual;

Returns result: 1,,3

Expected Result: 1,,5

@Gary_W has written about the problem with using that regex pattern to split strings, precisely because of how it treats empty tags

So I tried Case 3:

select regexp_replace('1,,3','(.*?)(,|$)',5,1,3)
from dual;

Returns Expected Result: 1,,5

But if I try this Case 4:

select regexp_replace('1,2,3','(.*?)(,|$)',5,1,2)
    from dual;

Returns result: 1,53

Expected Result: 1,5,3

I understand that I am doing something wrong with regular expression.Is there a way to make regexp_replace work in all above scenarios?

regexp-replace oracle sql regex
2 Answers

I think this would do what you want:

regexp_replace(col, '[^,]*(,|$)','5\1', 1, 3)

The idea is to capture 0 to N consecutive characters other than a comma, followed by a comma or the end of the string (the latter is captured). You then replace this with the target value, followed by the captured part.

user_106767160

If you want to stick to @Gary_W's pattern then you could do a (very!) similar thing to @GMB's proposal, but keeping the second capture group instead:

 regexp_replace(str, '(.*?)(,|$)', '5\2', 1, 3)

With some sample data:

with t (str) as (
            select '1,2,3' from dual
  union all select '1,,3' from dual
  union all select '1,2,3,4' from dual
  union all select '1,,3,4' from dual
  union all select '1,,,4,' from dual
  union all select ',,3' from dual
  union all select ',,3,' from dual
  union all select ',,,' from dual
  union all select '1' from dual
)
select str,
  regexp_replace(str, '(.*?)(,|$)', '5\2', 1, 3) as result
from t;

STR     RESULT    
------- ----------
1,2,3   1,2,5     
1,,3    1,,5      
1,2,3,4 1,2,5,4   
1,,3,4  1,,5,4    
1,,,4,  1,,5,4,   
,,3     ,,5       
,,3,    ,,5,      
,,,     ,,5,      
1       1

@GMB's approach gets exactly the same result for all of those, incidentally.

If you wanted to preserve empty third elements then you could use the regexp_substr version to apply the replace selectively:

with t as (...)
select str,
  case when regexp_substr(str, '(.*?)(,|$)', 1, 3, null, 1) is not null
    then regexp_replace(str, '(.*?)(,|$)', '5\2', 1, 3)
    else str
  end as result
from t;

STR     RESULT    
------- ----------
1,2,3   1,2,5     
1,,3    1,,5      
1,2,3,4 1,2,5,4   
1,,3,4  1,,5,4    
1,,,4,  1,,,4,    
,,3     ,,5       
,,3,    ,,5,      
,,,     ,,,       
1       1
user_2663040
Related