following issue:

I get a String as such "512, 986, 571, 665" transferred as parameter, basically a set of 3 digit numbers and these need to be returned sorted from highest to lowest.

I have only found answers pertaining such an issue when the String is found in a table and not given as parameter to a function

CodePudding user response：

Here's one option: split input string into rows (that's what subquery in lines #4 - 6 does), and then aggregate them back using listagg with appropriate order by clause (line #3).

SQL> with test (col) as
  2    (select '512, 986, 571, 665' from dual)
  3  select listagg(val, ', ') within group (order by val) result
  4  from (select to_number(trim(regexp_substr(col, '[^,] ', 1, level))) val
  5        from test
  6        connect by level <= regexp_count(col, ',')   1
  7       );

RESULT
--------------------------------------------------------------------------------
512, 571, 665, 986

SQL>

CodePudding user response：

create or replace TYPE t_numlist IS table OF varchar2(32700);


create or replace function sort_num_string(p_strnum in varchar2) return varchar2 is
    l_numlist t_numlist:=t_numlist();
    v_nuber_count number;
    v_strbucket varchar2(32700):='';
    v_strnum varchar2(32700):=p_strnum;
    b_bool boolean:=true;
begin 
    v_nuber_count :=length(v_strnum)-length(replace(v_strnum,',','')) 1;
    l_numlist.extend(v_nuber_count);
    for i in 1..v_nuber_count loop
        l_numlist(i) := regexp_substr(v_strnum,'[0-9]{3}');
        v_strnum :=trim(substr(translate(v_strnum,',',' '),4));
    end loop;
    for i in (select * from table(l_numlist) order by 1)loop
        if b_bool then v_strbucket:=i.column_value;
                  else v_strbucket:=i.column_value||', '||v_strbucket;
        end if;
        b_bool:=false;
    end loop;
    
    RETURN v_strbucket;

exception
    when others then dbms_output.put_line('wrong entry');
end;

Test:

begin
    dbms_output.put_line(sort_num_string('234, 567, 908, 992, 723, 531, 555'));
end;

Result:

992, 908, 723, 567, 555, 531, 234


PL/SQL procedure successfully completed.

CodePudding user response：

One way would be to use the ability of xmltable to parse a comma-separated list into XML elements, then use its getclobval() method to convert each element into a string, then finally re-aggregate the strings using listagg:

create or replace function sort_number_list
    ( p_list varchar2 )
    return varchar2
as
    sorted_list varchar2(4000);
begin
    select listagg(x.column_value.getclobval(), ', ') within group (order by to_number(x.column_value.getclobval()))
    into   sorted_list
    from   xmltable(p_list) x;
    
    return sorted_list;
end sort_number_list;

Test:

select sort_number_list('97,3,-123,4')
from dual;

SORT_NUMBER_LIST('97,3,-123,4')
------------------------------------------------------------
-123, 3, 4, 97

CodePudding user response：

A programmatic approach would be to use each element in the list as the index of an associative array (so integers only, or it'll break), which will have the effect of sorting it, then loop through the resulting array constructing a new list:

create or replace function sort_number_list
    ( p_list varchar2 )
    return varchar2
as
    type aa is table of number index by pls_integer;
    sort_tab aa;
    sorted_list varchar2(4000);
begin
    for i in 1..regexp_count(p_list, ',')  1 loop
        sort_tab(trim(regexp_substr(p_list,'[^,] ', 1, i))) := i;
    end loop;

    for i in indices of sort_tab loop
        sorted_list := sorted_list || ', ' || i;
    end loop;

    return ltrim(sorted_list,', ');
end sort_number_list;

I've used the indices of syntax added in 21c which makes looping around an associative array less verbose.