SELECT COLUMN_VALUE FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
- SQL> select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) from dual
- 2 connect by rownum<=length('1,2,3,4,5')-length(replace('1,2,3,4,5',','))+1
- 3 ;
- REGEXP_SUBSTR('1,2,3,4,5','[^,
- ------------------------------
- 1
- 2
- 3
- 4
- 5
- CREATE OR REPLACE FUNCTION fn_split (p_str IN CLOB, p_delimiter IN VARCHAR2)
- RETURN ty_str_split
- IS
- j INT := 0;
- i INT := 1;
- len INT := 0;
- len1 INT := 0;
- str VARCHAR2 (4000);
- str_split ty_str_split := ty_str_split ();
- 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);
- str_split.EXTEND;
- str_split (str_split.COUNT) := str;
- IF i >= len
- THEN
- EXIT;
- END IF;
- ELSE
- str := SUBSTR (p_str, i, j - i);
- i := j + len1;
- str_split.EXTEND;
- str_split (str_split.COUNT) := str;
- END IF;
- END LOOP;
- RETURN str_split;
- END fn_split;