oracle自定义字符串分割函数

oracle⾃定义字符串分割函数思路:
oracle没有提供类似java中split的函数来将字符串分割成数组,需要开发者⾃⼰搞定.
⾸先定⼀个数组类型,再定义⼀个函数或存储过程,处理字符串分割操作,
⼀个具体的实例如下:
--⾃定义数组类型:
CREATE OR REPLACE
TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
/
--⾃定义字符串分割函数,返回分组后的数组
CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)
RETURN varchar2varray
IS
len INTEGER := LENGTH (substring);
lastpos INTEGER := 1 - len;
pos INTEGER;
num INTEGER;
i INTEGER := 1;
ret varchar2varray := varchar2varray (NULL);
v_str VARCHAR2 (32767);
/**⾃定义split函数,将指定的字符串按指定的标志符分割成字符数组*/
BEGIN
LOOP
pos := INSTR (STRING, substring, lastpos + len);
IF pos > 0
THEN --found
num := pos - (lastpos + len);
ELSE --not found
num := LENGTH (STRING) + 1 - (lastpos + len);
END IF;
IF i > ST
THEN
ret.EXTEND;
END IF;
v_str := SUBSTR (STRING, lastpos + len, num);
--DBMS_OUTPUT.put_line (v_str);
ret(i) := v_str;
EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;
RETURN ret;
END;
/
--调⽤⽅式:
SELECT *
FROM TABLE (CAST (f_strsplit ('ABC$DEFGH$IJKLMN$OPQRST', '$') AS varchar2varray ) )
将输出:
column_value
--------------
ABC
DEFGH
IJKLMN
OPQRST
也可以采⽤如下⽅式得到数组中的值
FOR cur_str IN
(SELECT *
FROM TABLE
(CAST
(f_strsplit ('ABC$DEFGH$IJKLMN$OPQRST', '$') AS varchar2varray )
))
LOOP
DBMS_OUTPUT.put_line (cur_str.column_value);
--其他赋值操作
END LOOP;
转载⾃：。