oracle的plsql中如何实现字符串里面的数字拆分，排序？

[hackthissite]

需求描述：
存储过程接到变量值字符串"123,321,56,5654,232,6767"
把这个字符串里面的数值由小到大升序排序，拼成“56,123,232,321,6767” ？

高人指点。。。。

[luoyoumou]

写个函数，自己整！

[hackthissite]

给个提示撒，我本路出家……

[minitoy]

SQL> set serveroutput on
SQL> 
SQL> create or replace procedure proc_sort(i_str in varchar2)
  2  as
  3  v_str varchar2(4000);
  4  begin
  5   select wm_concat(num) into v_str from
  6    (select substr(','||i_str||',',instr(','||i_str||',',',',1,rownum)+1,instr(','||i_str||',',',',1,rownum+1)-instr(','||i_str||',',',',1,rownum)-1)  num
  7       from dual connect by rownum<length(translate(','||i_str||',',',1234567890',','))
  8      order by to_number(substr(','||i_str||',',instr(','||i_str||',',',',1,rownum)+1,instr(','||i_str||',',',',1,rownum+1)-instr(','||i_str||',',',',1,rownum)-1)));
  9    dbms_output.put_line(v_str);
 10  
 11  end;
 12  /
 
Procedure created
 
SQL> exec proc_sort('123,321,56,5654,232,6767');
 
56,123,232,321,5654,6767
 
PL/SQL procedure successfully completed
 
SQL> 

[hackthissite]

你这太强悍了[Quote=引用 4 楼 minitoy 的回复:]

SQL code
SQL> set serveroutput on
SQL>
SQL> create or replace procedure proc_sort(i_str in varchar2)
2 as
3 v_str varchar2(4000);
4 begin
5 select wm_concat(num) into v_str from
6 ……
[/Quote]

[ojuju10]

create or replace function sortstring(instring in varchar2)
return varchar2
is
v_result varchar2(200);
begin

select max(name) into v_result from 
(
select wm_concat(name)over(order by to_number(name)) as name  from
(
select substr(a.name,b.id+1,instr(a.name,',',b.id+1)-b.id-1) as name from
 (select ','||instring||',' as name from dual) a,
 (select rownum as id  from dual connect by  rownum<=length(instring)+1) b
where substr(a.name,b.id,1)=','

)
);
return v_result;
end;


select sortstring('123,321,56,5654,232,6767') from dual;

[ojuju10]

 --字符串分离小程序 
drop table ta;
create table ta(id int,name varchar2(100));
insert into ta select 1, '123,321,56,5654,232,6767' from dual;
insert into ta select 2,'123,321,56' from dual;
commit;

select a.id,substr(a.name,b.id,instr(a.name||',',',',b.id)-b.id) as name  from ta a,
 (select rownum as id  from dual connect by  rownum<=100) b
where substr(','||a.name,b.id,1)=','
order by a.id
------------
id name
1	123
1	56
1	232
1	5654
1	321
1	6767
2	321
2	56
2	123

[hackthissite]

WMSYS.WM_CONCAT 这个函数的使用方法说明，哪里能知道？
我在网上找遍了也没找到

[Hi大鹏]

按照都好拆分，然后把拆分的字符编程数字排序