Oracle - regexp_replace with nulls in a comma separated Strings

Question

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?

How about: Don't store lists of numbers as delimited strings? SQL has a great way to store lists; it is called a table, not a string. — Gordon Linoff, Jun 10 '20 at 16:25
Your case 4 didn't work as expected because the comma was "consumed" by the pattern match but not put back with the replacement string. In the spirit of my post that you refer to, Alex's post below puts the trailing character (captured group 2) back. — Gary_W, Jun 11 '20 at 13:35

score 1 · Accepted Answer · answered Jun 10 '20 at 16:36

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.

Alex Poole · Answer 2 · 2020-06-10T17:04:11.247

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

Oracle - regexp_replace with nulls in a comma separated Strings

2 Answers2