Create split and splitstr functions in Oracle, splitsplitstr

Create split and splitstr functions in Oracle, splitsplitstr

It is recommended that SQL statements be created in sequence.

/*************************************** Name: split * author: sean zhang. * date: 2012-09-03. * function: returns the table type after a string is separated by a specified character. * Parameters: p_list: string to be split. P_sep: delimiter. It is a comma by default. You can also specify characters or strings. * Example: select * from users where u_id in (select column_value from table (split ('1, 2') returns two rows of data whose u_id is 1 and 2. **************************************// * Create a table type */create or replace type tabletype as table of varchar2 (32676) /* create the split function */create or replace function split (p_list clob, p_sep varchar2: = ',') return tabletype pipelinedis l_idx pls_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: return the node string separated by a specified character. * Parameters: str: string to be split. I: return the nth node. If I is 0, all characters in str are returned. If I exceeds the number of splits, null is returned. Sep: delimiter. It is a comma by default. You can also specify characters or strings. If the specified separator does not exist in str, the characters in sep are returned. * Example: select splitstr ('abc, def ', 1) as str from dual; get abc select splitstr ('abc, def', 3) as str from dual; ************************************** // * create the splitstr function */create or replace function splitstr (str in clob, I in number: = 0, sep in varchar2: = ',') return varchar2 is 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 into 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, identifier)

Select split ('a, B, c, e, d, f, G') arrData from dual;

By default, commas (,) are used to separate data, which can be customized. For example, select split ('x-rapido & Lemon ',' & ') arrData from dual;

Opening set

By default, commas (,) are used to separate data, which can be customized. For example, select split ('x-rapido & Lemon ',' & ') arrData from dual;

Example:Splitstr (string, obtained node subscript, separator)

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; -- Null String

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