Oracle's Split function

There is no split function in PL/SQL and you need to write it yourself.

Code:

Create or replace type Type_split as Table of VARCHAR2 (50); --Create a type, and if you want the split function to be generic, set its size larger.

--Create function

Create or Replace function split

(

P_list VARCHAR2,

P_sep varchar2: = ', '

) Return Type_split pipelined

Is

L_idx Pls_integer;

V_list varchar2 (): = P_list;

Begin

Loop

L_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, Saibei Snow ', ', '));

Column_value

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

Northsnow

Saibei of Snow

Sql>


Add:
What do-----pipelined keywords mean?
What do you mean by----pipe row?

Pipelined reputation This function is pipe, if so famous, you must use pipe row to return the data, the general function of the last "return variable", it becomes "return".

The function of pipelined is mainly to improve the efficiency, do not wait for all the data to be processed before returning to the client, it is the edge processing side return. For interactions with large data volumes.

Oracle's Split function