Oracle character Split function, returns the result set

Oracle cannot support directly returning a table type like MSSQL, so create a custom type first. This is used for nested tables (Nested table).

--Nested tablecreate or replace TYPE split_str is TABLE of VARCHAR,/--functioncreate or replace FUNCTION fn_split (    P_str VARCHAR2, P_delimiter VARCHAR2) RETURN split_str pipelinedas v_str VARCHAR (4000): = P_STR;    V_index number; V_sublength number; BEGIN--P_delimiter is null IF p_delimiter are null then for x in 1..LENGTH (V_STR) LOOP PIP        E ROW (SUBSTR (v_str,x,1));        END LOOP;    RETURN;       END IF;    V_index: = INSTR (V_str,p_delimiter);        While V_index <> 0 LOOP PIPE ROW (SUBSTR (v_str,1,v_index-1));        V_sublength: = Length (V_STR)-(V_index + length (p_delimiter)-1);        V_STR: = SUBSTR (v_str,-v_sublength,v_sublength);    V_index: = INSTR (V_str,p_delimiter);    END LOOP;    PIPE ROW (V_STR); RETURN; end;/--(second way better) SELECT fn_split (' [email protected]@[email protected] @it ', ' @@ ') from dual; SELECT * from Table (Fn_split (' [email protected]@[email protected] @it ', ' @@ '));

From: The teacher of ignorance