By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TYPE string_list AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION split_String(
i_str IN CLOB,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN string_list DETERMINISTIC PIPELINED
AS
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
PIPE ROW (SUBSTR( i_str, p_start, p_end - p_start ));
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
PIPE ROW (SUBSTR( i_str, p_start, c_len - p_start + 1 ));
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
NULL;
END;
/
SELECT column_value
FROM TABLE(split_string('abc , xyz', ' , ') );
| COLUMN_VALUE |
|---|
| abc |
| xyz |