0

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 !!

Chase Dan
  • 3
  • 1
  • 1
    FWIW...The column is of varchar2 datatype. – Chase Dan Jan 29 '21 at 00:35
  • Welcome to the SO community. As your question is short on details you may benefit by looking at [ask]. In particular why are you sorting and what is the expected sorted output of 1 row. Or do you actually mean you are trying to parse it into individual elements from a comma separated list of elements. – Belayer Jan 29 '21 at 00:44
  • And sorry for lack of more details. The reason behind my sort is that I'm comparing the source column value 'M013,M007,M019,YYY,M018,XXX,999' to the result of a listagg function output on a column from a different table. The listagg function sorts and outputs the values like this '999,M007,M013,M019,M018,XXX,YYY' (after sorting?). So, when I compare these two columns, even though the values are same, the comparison fails as they are not sorted. I'm not trying to parse them to individual elements. – Chase Dan Jan 29 '21 at 01:12
  • Friendly note to let you know that when you add details, edit the original post, don't do it in the comments. There is no formatting really in the comments and more eyes will see the change in the original post. – Gary_W Jan 29 '21 at 21:11
  • sure will do that for future posts @Gary_W – Chase Dan Jan 29 '21 at 23:53

1 Answers1

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 
        ) ;
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Thanks @Belayer. That was very helpful and it returns data as expected. I'm going to implement this on a table column instead of predefined text 'M013,M007,M019,YYY,M018,XXX,999' and see if it works. Thanks again for your response!! – Chase Dan Jan 29 '21 at 23:49
  • @ChaseDan if the answer helped you please accept it. That helps future questioners that have the same or similar question. – Belayer Jan 31 '21 at 00:44