Oracle 分割字符串函数
http://lwl0606.cmszs.com/archives/oracle-split-string-function.html
CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
/
CREATE OR REPLACE FUNCTION sf_split_string (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);
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 > ret.LAST THEN
ret.EXTEND;
END IF;
ret(i) := SUBSTR(string, lastpos + len, num);
EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;
RETURN ret;
END;
/
SQL> select * from table (cast (
2 sf_split_string('ABC,DEFGH,IJKLMN,OPQRST', ',') as Varchar2Varray) );
COLUMN_VALUE
----------------------------------------
ABC
DEFGH
IJKLMN
OPQRST
SQL> select * from table (cast (
2 sf_split_string('ABC//DEFGH/IJKLMN//OPQRST', '//') as Varchar2Varray) );
COLUMN_VALUE
----------------------------------------
ABC
DEFGH/IJKLMN
OPQRST
http://lwl0606.cmszs.com/archives/oracle-split-string-function.html