Oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abc defg hijkl nmopqr stuvw xyz”,分隔符是空格,但空格个数不定。 源代码:
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF
CREATE OR REPLACE FUNCTION fn_var_split (
p_str IN
p_delimiter IN
)
RETURN ty_str_split
IS
j
len
str
str_split ty_str_split := ty_str_split ();
v_str
BEGIN
len := LENGTH (v_str);
WHILE len >
LOOP
j := INSTR (v_str, p_delimiter,
IF j =
THEN
str := SUBSTR (v_str,
len :=
str_split.EXTEND;
str_split (str_split.COUNT) := str;
ELSE
str := SUBSTR (v_str,
v_str := LTRIM (LTRIM (v_str, str), p_delimiter);
len := LENGTH (v_str);
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP;
RETURN str_split;
END fn_var_split;
/
测试: 结果: 1 12 123 1234 12345
DECLARE
CURSOR c
IS
SELECT * FROM TABLE (CAST (fn_var_split (';1;12;;123;;;1234;;;;12345;', ';') AS ty_str_split));
r c%ROWTYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.put_line (r.column_value);
END LOOP;
CLOSE c;
END;
/