DECLARE

v_str VARCHAR2(4000) := 'aa,bb,cc,dd,ee,';

v_length PLS_INTEGER;

TYPE typ_str_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;

v_str_arr typ_str_tab;

j PLS_INTEGER := 0;

v_cnt PLS_INTEGER := 0;

v_str_length PLS_INTEGER;

BEGIN

v_length := regexp_count(v_str, ',') + 1;

v_str_length := length(v_str);

FOR i IN 1 .. length(v_str) LOOP

IF substr(v_str, i, 1) = ',' THEN

IF j = 0 THEN

v_cnt := v_cnt + 1;

v_str_arr(v_cnt) := substr(v_str, j, i - 1);

j := i;

ELSE

v_cnt := v_cnt + 1;

v_str_arr(v_cnt) := substr(v_str, j + 1, i - j - 1);

j := i;

END IF;

--最后一个字符处理较为特殊

IF i = v_str_length THEN

v_str_arr(v_cnt + 1) := substr(v_str, j + 1, i - j);

END IF;

END LOOP;

dbms_output.put_line('xx = ' ||

regexp_instr(v_str, ',', 1, v_length - 1));

dbms_output.put_line(v_str_arr.count);

FOR z IN 1 .. v_str_arr.count LOOP

dbms_output.put_line(v_str_arr(z));

END LOOP;

END;

ITPub博客

无聊写的简单用PLSQL分割字符串的脚本