Returns the result set of the ORACLE character split function.

Returns the result set of the ORACLE character split function.

ORACLE does not support direct return of table type like MSSQL. Therefore, you must first create a custom type. This statement uses a Nested Table ).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

-- Nested Table Create or replace type split_str is table of varchar (100 ); /   -- Function Create or replace function fn_Split ( P_Str VARCHAR2, P_Delimiter VARCHAR2 ) RETURN split_str PIPELINED AS V_Str VARCHAR (4000): = p_Str; V_Index NUMBER; V_SubLength NUMBER; BEGIN -- P_Delimiter is null IF p_Delimiter IS NULL THEN FOR x IN 1 .. LENGTH (v_Str) LOOP Pipe 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; /   -- Using (the second method is better) SELECT fn_split ('this @ is @ it', '@') FROM dual; SELECT * FROM Table (fn_split ('this @ is @ it ','@@'));