发表于: 2007.02.03 17:24
分类: oracle
出处: http://hanjs.itpub.net/post/28217/260065
---------------------------------------------------------------
下面是转自itpub上zhouwf0726的一篇文章上对于字符串的处理，但是我个人还是认为写一个字定义函数来处理比较通用。
/*
怎样支掉字符串中逗号间重复的字符
如 ',1,2,5,9,1,2,5,9,1,2,9,1,2,9,1,2,3,9,1,2,3,9,1,2,9,1,2,9,1,2,3,9,1,2,3,9,'怎样支掉字符串中逗号间重复的字符,并将字符升序排列，得到
',1,2,3,5,9,'
百思不得其解，是高手的试一下。
解答：
select col from(
select sys_connect_by_path(col,',')||',' col,level from(
select col,row_number() over(order by rownum) rn from (
select distinct substr(col,instr(col,',',1,rownum)+1,instr(col,',',1,rownum+1)-instr(col,',',1,rownum)-1) col from (
select ',1,2,5,9,1,2,5,9,1,3,9,' col from dual
) connect by rownum<length(translate(col,','||col,','))
)
)
connect by prior rn = rn -1 order by level desc
) where rownum=1
*/
利用自定义函数实现一，利用函数返回数组．
create or replace function f_split_string(var_str in string,var_split In String) return t_ret_table
is
var_out t_ret_table;
var_tmp varchar2(4000);
var_element varchar2(4000);
begin
var_tmp := var_str;
var_out := t_ret_table();
--如果存在匹配的分割符
while instr(var_tmp,var_split)>0 loop
var_element := substr(var_tmp,1,instr(var_tmp,var_split)-1);
var_tmp := substr(var_tmp,instr(var_tmp,var_split)+length(var_split),length(var_tmp));
var_out.extend(1);
var_out(var_out.count) := var_element;
end loop;
var_out.extend(1);
var_out(var_out.count) := var_tmp;
return var_out;
end f_split_string;
/*
create or replace type t_ret_table is table of varchar2(20);
字符串分割的函数，返回数组，但是也可以使用语句查询
select * from table(f_split_string('hanjs-zhaos','-'));
*/
利用 pipelined Function 函数实现.
create or replace function f_split(var_str in string,var_split In String) return t_ret_table PIPELINED
as
var_tmp varchar2(4000);
var_element varchar2(4000);
n_length Number := length(var_split);
begin
var_tmp := var_str;
while instr(var_tmp,var_split)>0 loop
var_element := substr(var_tmp,1,instr(var_tmp,var_split)-1);
var_tmp := substr(var_tmp,instr(var_tmp,var_split)+n_length,length(var_tmp));
pipe row(var_element);
end loop;
pipe row(var_tmp);
return;
end f_split;
/*
create or replace type t_ret_table is table of varchar2(20);
字符串分割的函数，使用语句查询
select * from table(f_split('hanjs-zhaos','-'));
*/
对于需要排序的，完全可以在查询时体现．
注意：查询出来的列名为 Column_Value
Kyle
