失乐园

技术之路

BlogJava 联系 聚合 管理
  19 Posts :: 44 Stories :: 40 Comments :: 0 Trackbacks
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)


           RETURN ty_str_split PIPELINED


IS


           j INT := 0;


    i INT := 1;


    len INT := 0;


    len1 INT := 0;


    str VARCHAR2 (4000);


BEGIN


           len := LENGTH (p_str);


    len1 := LENGTH (p_delimiter);





           WHILE j < len


           LOOP


               j := INSTR (p_str, p_delimiter, i);





               IF j = 0


               THEN


                   j := len;


            str := SUBSTR (p_str, i);


            PIPE ROW (str);





                   IF i >= len


                   THEN


                EXIT;


            END IF;


               ELSE


            str := SUBSTR (p_str, i, j - i);


            i := j + len1;


            PIPE ROW (str);


        END IF;


    END LOOP;





           RETURN;


END fn_split;

使用方法:
SELECT * FROM TABLE (fn_split ('INC1316086732328,INC1316086737562,INC1316083148750,INC1316072987015,INC1316053713729', ','))
posted on 2011-09-16 09:26 狄浩 阅读(1049) 评论(0)  编辑  收藏

只有注册用户登录后才能发表评论。


网站导航: