Oracle split Function

PLSQL does not have the split function. You need to write it yourself. Code: createorreplacetypetype_splitastableofvarchar2 (50); -- create a type. To make the split function more universal, set the size to a greater value. -- Create functioncreateorreplacefunctionsplit (p_listvarchar2, p_sepvarchar2:

PL/SQL does not contain the split function. You need to write it yourself. Code: createorreplacetypetype_splitastableofvarchar2 (50); -- create a type. To make the split function more universal, set the size to a greater value. -- CREATE function createorreplacefunctionsplit (p_listvarchar2, p_sepvarchar2:

PL/SQL does not contain the split function. You need to write it yourself.

Code:

Create or replace type type_split as table of varchar2 (50); -- create a type. To make the split function more universal, set the size to a higher value.

-- Create a function

Create or replace function split

(

P_list varchar2,

P_sep varchar2: = ','

) Return type_split pipelined

Is

Rochelle idx pls_integer;

V_list varchar2 (50): = p_list;

Begin

Loop

Rochelle idx: = instr (v_list, p_sep );

If l_idx> 0 then

Pipe row (substr (v_list, 1, l_idx-1 ));

V_list: = substr (v_list, l_idx + length (p_sep ));

Else

Pipe row (v_list );

Exit;

End if;

End loop;

Return;

End split;

Test:

SQL> select * from table (split ('northsnow, xue ',','));

COLUMN_VALUE

--------------------------------------------------

Northsnow

Snow in Sebei

SQL>


Supplement:
----- What does the PIPELINED keyword mean?
---- What does pipe row mean?

Pipelined indicates that this function is pipe. If it is so famous, you must use the pipe row Method to return data. The "return variable" at the end of the conventional function becomes "return ".

The pipelined function is mainly used to improve efficiency and return to the client without waiting for all the data to be processed. It is used to process and return while processing, and is suitable for interactions with large data volumes.