Oracle Split String function

CREATE OR REPLACE TYPE str_split is TABLE of VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION splitstr (p_string in VARCHAR2, P_delimiter in VARCHAR2)
Return Str_split
pipelined
As
V_length Number: = Length (p_string);
V_start number: = 1;
V_index number;
BEGIN
while (V_start <= v_length)
LOOP
V_index: = INSTR (p_string, P_delimiter, V_start);


IF V_index = 0
THEN
PIPE ROW (SUBSTR (p_string, V_start));
V_start: = v_length + 1;
ELSE
PIPE ROW (SUBSTR (p_string, V_start, V_index-v_start));
V_start: = V_index + 1;
End IF;
End LOOP;


return;
End Splitstr, after you've created it, let's test it, for example, execute the following sql:


SELECT * FROM table (splitstr (' hello,cnblogs! ', ', '); its output is a two-row table, as shown in the following figure:






To display rows as columns:


Select A.column_value V1,b.column_value v2 from
(select RowNum rn,t.* from table (splitstr (' hello,cnblogs! ', ', ') t)) A,
(select RowNum rn,t.* from table (splitstr (' hello,cnblogs! ', ', ') t)) b
where A.rn=1 and b.rn=2 as shown: