Remove duplicate substring from a string in oracle

Question

I have strings like below in my table

2001,2452,2452,2421,2421,2495

2001,2483,2421,2421,2482

2001,2420,2421,2421,2425

2001,2420,2421,2421,2422

2001,2452,2452,2421,2421,2464

I want to remove the repeated numbers like 2452 and 2421 and show them only once in the data like

2001,2452,2421,2495

2001,2483,2421,2482

2001,2420,2421,2425

2001,2420,2421,2422

2001,2452,2421,2464

Has anyone done something like this? please let me know how to solve this

Thanks!

You should seriously consider moving away from storing CSV data like this. Get each CSV value onto a separate row. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 8, 2021 at 5:02
Is APEX installed in your database? You could write your own but apex_util.string_to_table and apex_util.table_to_string and/or depending on APEX version apex_util.split and listagg could be used. Assuming that fixing the data model isn't an option. — Justin Cave
– Justin Cave, Commented Jan 8, 2021 at 5:14

Popeye · Accepted Answer · 2021-01-08 05:16:45Z

1

In Oracle SQL, You can use the hierarchy query and listagg as follows:

select str, listagg(str_distinct, ',') within group (order by 1) as distinct_str from
(select distinct str, regexp_substr(str,'[^,]+',1,column_value) str_distinct from cte
cross join table( 
        cast(multiset( 
            select level lvl 
            from dual 
            connect by level <= regexp_count(str, '[^,]+')) 
        as sys.odcivarchar2list) 
        ) lvls)
group by str;

db<>fiddle for one of the input string.

answered Jan 8, 2021 at 5:16

Popeye

36k4 gold badges12 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Remove duplicate substring from a string in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related