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>
create or replace function sortstring(instring in varchar2) return varchar2 is v_result varchar2(200); beginselect 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;
--字符串分离小程序 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
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>
create or replace function sortstring(instring in varchar2)
return varchar2
is
v_result varchar2(200);
beginselect 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;
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
我在网上找遍了也没找到