Sort a string of Numbers 2

[mili3]

Hi

I need sort a string of numbers. The input is like '140,120,130,110' I need the output as a sorted string like ('110,120,130,140'). The String will have 1 to 4 vaules , and not more than 4 . And the number is always 3 characters long (110) . I need write a function or procedure to sort. How do I do it ? Any suggestions ?

Mili

 

[SantaMufasa]

Mili,

Under the rather favourable circumstances of your need (i.e., all values between 100 and 999), this code should work fine for you. Plus, it will handle any number of values (0-999) within the 100-999 restriction.):

create or replace function str_sort (str_in varchar2) return varchar2 is
    hold_str    varchar2(4000);
    result_str  varchar2(4000);
    min_str     varchar2(4000);
    sql_str     varchar2(4000);
begin
    hold_str       := str_in;
    while hold_str is not null loop
        sql_str    := 'select least('||hold_str||') from dual';
        execute immediate sql_str into min_str;
        result_str := trim(both ',' from result_str||','||min_str);
        hold_str   := trim(both ',' from replace(replace(hold_str,min_str,null),',,',','));
    end loop;
    return result_str;
end;
/

Function created.

col x format a50
select str_sort('140,120,130,110') x from dual;

X
--------------------------------------------------
110,120,130,140

select str_sort('900,100,800,200,700,300,600,400,500')x from dual;

X
--------------------------------------------------
100,200,300,400,500,600,700,800,900

select str_sort('140') from dual;

X
--------------------------------------------------
140

select str_sort('') from dual;

X
--------------------------------------------------

Let us know your reactions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[mili3]

Oh my god ! This is excellent & Brilliant solution. It works like magic for me. Thanks Mufasa !! I learnt something new today.

 

[mili3]

Hi,

I want the above code to work for one more condition. I got a string like ('246,224,224,232'). When a value is repeated this is not working. Any ideas ????

Thanks
Mili

 

[SantaMufasa]

Here is your code adjustment for your new scenario:

create or replace function str_sort (str_in varchar2) return varchar2 is
    hold_str    varchar2(4000);
    result_str  varchar2(4000);
    min_str     varchar2(4000);
    sql_str     varchar2(4000);
    loc         number;
begin
    hold_str       := str_in;
    while hold_str is not null loop
        sql_str    := 'select least('||hold_str||') from dual';
        execute immediate sql_str into min_str;
        result_str := trim(both ',' from result_str||','||min_str);
        loc        := instr(hold_str,min_str);
        hold_str   := substr(hold_str,1,loc-1)||substr(hold_str,loc+3);
        hold_str   := trim(both ',' from replace(hold_str,',,',','));
    end loop;
    return result_str;
end;
/

Function created.

select str_sort('246,224,224,232') from dual;

STR_SORT('246,224,224,232')
---------------------------
224,224,232,246

Let us know if this suits your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[mili3]

Thank you !

 

[mili3]

This is absolutely perfect ! thank you .

 

[SantaMufasa]

My pleasure. I enjoyed doing it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.