功能就是用某一标记分割字符串,并返回指定的第几个。
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2,index_i IN int)
RETURN VARCHAR2
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
index_a 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);
IF i >= len or index_a = index_i
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
index_a := index_a + 1 ;
IF index_a = index_i
THEN
EXIT;
END IF;
END IF;
END LOOP;
RETURN str;
END fn_split;
效果见图: