oracle根据_分割字符串,oracle分割字符串函数

本站寻求有缘人接手，详细了解请联系站长QQ1493399855

create or replace function f_split        (        /*功能：分割字符串          实现：通过分割符对字符串进行分割          编写人： 20111205              */        var_source_string in varchar2,--要分割的字符串        var_separator     in varchar2,--分隔符        var_number        in number:=0   --输分割的第几个值，如果为0则表示全部输出        )    return TABLETYPE_VARCHAR2 is   v_source_string varchar2(2000);   v_separator_position integer;--分割符位置   v_source_length integer;--要分割的字符串的长度   v_separator_length integer;--分割符的长度   v_result TABLETYPE_VARCHAR2:=TABLETYPE_VARCHAR2();   v_subset varchar2(2000);   v_count integer:=0; begin     if trim(var_separator) is null then       raise_application_error(-20001,'必须输入分割符！');     end if;     v_source_string:=trim(var_source_string);     --判断分割符的位置     v_separator_position:=instr(v_source_string,var_separator);     --分隔符的长度     v_separator_length:=length(var_separator);     --源分割字符串的长度     v_source_length:=length(var_source_string);     --源字符串最后必须有分隔符     if substr(v_source_string,v_source_length-v_separator_length+1,v_separator_length)<>var_separator then       raise_application_error(-20002,''''||v_source_string||'''字符串最后必须有分隔符！');     end if;       while v_separator_position>0 loop       --截取分割后的字符串       v_subset:=substr(v_source_string,1,v_separator_position-1);       if trim(v_subset) is not null then         v_result.extend;--数组添加一个空下标         v_result(v_result.last):=v_subset;--为数组最后一位赋值       end if;       v_source_string:=substr(v_source_string,v_separator_position+v_separator_length);       v_separator_position:=instr(v_source_string,var_separator,1);       v_count:=v_count+1;       if var_number=v_count then         v_result.delete;         v_result.extend;--数组添加一个空下标         v_result(v_result.last):=v_subset;--为数组最后一位赋值         return(v_result);       end if;     end loop;     return(v_result); end f_split; /* 测试代码： declare    -- Local variables here   i integer;   results tabletype_varchar2; begin   -- Test statements here   results:=f_split('"AAB999":"123","AAB004":"123","AAB003":"123","AAE006":"",   "AAE048":"","AAE051":"","AAE049":"","AAE047":"","AAB007":"","AAB008":"","AAB009":"",   "AAB023":"","AAB013":"123","AAB014":"123","AAE159":"","AAE007":"","AAB030":"123",   "AAZ066":"123","AAE004":"","AAE005":"","AAZ198":"","AAB301":"","AAB078":"",   "AAE013":"","AAB022":"","AAB021":"","AAB065":"","AAB006":"5","AAA149":"2",   "AAB020":"143","AAB019":"91","AAE011":"经办人","AAA027":"统筹区编码",', ',',7);   for i in 1 .. results.count loop     dbms_output.put_line(results(i));   end loop; end; */ /