sorting - sort multi value column in oracle table

Question

I'm trying to sort on a column in oracle table. The column value is 'M013,M007,M019,YYY,M018,XXX,999'. I'm trying to sort the values with in the column before comparing it to another column which already has the data sorted. I've tried multiple hash/MD5 and few other options, but didn't help. Any help is appreciated !!

question from:https://stackoverflow.com/questions/65947175/sort-multi-value-column-in-oracle-table

Answer 1

What you have is a comma separated string of random elements. As you indicated the listagg function can sort the results. I think your best bet then is to parse the string into individual elements then let listagg rebuild the string with sorted elements. (See fiddle)

with test(str) as 
     ( select 'M013,M007,M019,YYY,M018,XXX,999' from dual) 
select listagg(estr,',') within group (order by estr)
   from (select regexp_substr(str,'[^,]+', 1, level) estr
           from test connect by regexp_substr(str, '[^,]+', 1, level) is not null 
        ) ;