Oracle creates split and SPLITSTR functions

SQL statements are best executed in turn to create

/************************************** * Name:split * Author:sean Zhang. * date:2012-09-03. * Function: Returns the type of the table after the string is split by the specified character.                * Parameters:p_list: String to be split. P_SEP: delimiter, default comma, or character or string can be specified. * Example:select * from the users where u_id in (select Column_value from table (Split ('))) returns U_ID to 1 and 2 of two rows of data. * Create a table type */create or replace type Tabletype as Table of VARCHAR2 (32676)/* Create Spli t function */create or replace function split (P_list CLOB, p_sep varchar2: = ', ') return Tabletype Pipelinedis L_idx p   Ls_integer;      V_list varchar2 (32676): = 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;end;/************************************** * NAME:SPLITSTR * Author: Sean Zhang. * date:2012-09-03. * Function: Returns the string of the specified node after the specified character is split.                * Parameters:str: String to be split. I: Returns the first few nodes.                When I returns to 0, all characters in Str are returned empty when I exceeds the number of divisible numbers. Sep: delimiter, default comma, or character or string can be specified. Returns the characters in Sep when the specified delimiter does not exist in Str.  * Example:select splitstr (' Abc,def ', 1) as Str from dual;  Get ABC Select SPLITSTR (' Abc,def ', 3) as Str from dual;                                    Get empty **************************************//* Create SPLITSTR function */create or replace function splitstr (str in CLOB, I in Number: = 0, Sep in varchar2: = ', ') return VARCHAR2 I  s t_i number;  T_count number;  T_STR varchar2 (4000); begin If I = 0 then t_str: = str;  elsif InStr (str, SEP) = 0 Then t_str: = Sep;      else select COUNT (*) into t_count from table (Split (str, SEP));                If I <= t_count then select Str to T_STR from (select RowNum as item, column_value as Str From table (Split (str, SEP))) wheRE item = i;  End If;  End If; return t_str;end;

Example: Split (string, identity)

Select Split (' A,b,c,e,d,f,g ') arrdata from dual;

By default, commas are used to customize the modifications, such as:Select Split (' X-rapido & Lemon ', ' & ') arrdata from dual;

Open the collection.

By default, commas are used to customize the modifications, such as:Select Split (' X-rapido & Lemon ', ' & ') arrdata from dual;

Example:splitstr (string, Getting the node subscript, delimiter)

Select Splitstr (' X-rapido&lemon&jennifer ', 1, ' & ') word from dual; --X-rapido
Select Splitstr (' X-rapido&lemon&jennifer ', 2, ' & ') word from dual; --Lemon
Select Splitstr (' X-rapido&lemon&jennifer ', 3, ' & ') word from dual; --Jennifer
Select Splitstr (' X-rapido&lemon&jennifer ', 4, ' & ') word from dual; --empty string

Http://www.cnblogs.com/enjoycode/archive/2012/11/10/oracle_split.html

Oracle creates split and SPLITSTR functions