oracle自定义split函数,自定义oracle字符串分割函数split

(1)定义split_type类型：

CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (4000)

/

(2)定义split函数：

CREATE OR REPLACE FUNCTION ETL.split (

p_str IN VARCHAR2,

p_delimiter IN VARCHAR2 default(',') --分隔符，默认逗号

)

RETURN split_type

IS

j INT := 0;

i INT := 1;

len INT := 0;

len1 INT := 0;

str VARCHAR2 (4000);

my_split split_type := split_type ();

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);

my_split.EXTEND;

my_split (my_split.COUNT) := str;

IF i >= len

THEN

EXIT;

END IF;

ELSE

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

i := j + len1;

my_split.EXTEND;

my_split (my_split.COUNT) := str;

END IF;

END LOOP;

RETURN my_split;

END split;

/

(3)测试split函数：

select * from table (split('1,2,3,4',','))