Oracle 自定义split 函数实例详解
程序员文章站
2022-05-10 23:21:20
oracle 自定义split 函数
oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abc defg hijkl &n...
oracle 自定义split 函数
oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abc defg hijkl nmopqr stuvw xyz”,分隔符是空格,但空格个数不定。
源代码:
create or replace type ty_str_split is table of varchar2 (4000); create or replace function fn_var_split ( p_str in varchar2, p_delimiter in varchar2 ) return ty_str_split is j int := 0; len int := 0; str varchar2 (4000); str_split ty_str_split := ty_str_split (); v_str varchar2 (4000) := rtrim (ltrim (p_str, p_delimiter), p_delimiter); begin len := length (v_str); while len > 0 loop j := instr (v_str, p_delimiter, 1); if j = 0 then str := substr (v_str, 1); len := 0; str_split.extend; str_split (str_split.count) := str; else str := substr (v_str, 1, j - 1); v_str := ltrim (ltrim (v_str, str), p_delimiter); len := length (v_str); str_split.extend; str_split (str_split.count) := str; end if; end loop; return str_split; end fn_var_split; /
测试:
结果:
1 12 123 1234 12345
declare cursor c is select * from table (cast (fn_var_split (';1;12;;123;;;1234;;;;12345;', ';') as ty_str_split)); r c%rowtype; begin open c; loop fetch c into r; exit when c%notfound; dbms_output.put_line (r.column_value); end loop; close c; end; /
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!