0

I have a comma separated list of values (as one single string), which contains duplicates, and I want to remove them. I tried with REGEXP_REPLACE but for certain values it gives me a wrong result:

/* CORRECT */
select '308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' as str,
       regexp_replace('308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001',
                  '([^,]+)(,*\1)+',
                  '\1') as str_uq
from dual
union all 
/* FAULTY */
select '106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' as str,
       regexp_replace('106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701',
                  '([^,]+)(,*\1)+',
                  '\1') as str_uq
from dual

Running the above example, will provide the expected result for the first query, but for the second one, it just concatenates all the values without removing duplicates. I guess the fact that the values are starting with 1 has something to do with it.

Can you please help me out with the regex pattern? I cannot find the mistake...

Thank you!

2 Answers 2

1

In your 2nd example the values are unique, but you're searching for any repeated match and there's multiple matches:

'106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701'

It's not concatenating, it's removing some characters :-)

Usually adding a word boundary \b should work, but not in Oracle.

I found this is working '(.+?,)\1+', but you need to add a final comma to the string (fiddle)

/* CORRECT */
select '308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' as str,
       regexp_replace('308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' || ',',
                  --'(\b[^,]+)(,*\1)+', --should work, doesn't in Oracle
                  '(.+?,)\1+',
                  '\1') as str_uq
from dual
union all 
/* FAULTY */
select '106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' as str,
       regexp_replace('106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' || ',',
                  --'(\b[^,]+)(,*\1)+', --should work, doesn't in Oracle
                  '(.+?,)\1+',
                  '\1') as str_uq
from dual
2
  • Thanks for the explanation! Even with the comma at the end, the second query still doesn't remove the duplicate. The first and forth accounts are identical, and they are both returned by fiddle, and my oracle instance too.
    – ddaniel
    Jun 3, 2015 at 11:47
  • 1
    @ddaniel: I didn't notice that this was repeated. I'm not that good in Regular Expressions, but afaik you can't do what you want in a RegEx.
    – dnoeth
    Jun 3, 2015 at 12:14
0

Indeed, as dnoeth said, it seems that I cannot achieve the expected behavior using only REGEXP, so I built a function that will do this. I'll post it here, maybe it is useful to somebody else too.

create or replace function str_remove_duplicates (str in varchar2, separator in varchar2) 
    return varchar2
is
    regex_pattern varchar2(5);
    result varchar2(255);
begin

    regex_pattern := '[^' || nvl(separator, ',') || ']+'; -- '[^,]+'

    select listagg(acc, separator) within group (order by acc) into result
    from (
            select distinct acc from (
                                        select regexp_substr(str, regex_pattern, 1, level) as acc from dual
                                        connect by regexp_substr(str, regex_pattern, 1, level) is not null
                                     )
    );

    return result;
end str_remove_duplicates;

All the best!

Daniel

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.