Create functions to join and split strings in SQL

There is an article on creating join and split string in SQL url: http://articles.techrepublic.com.com/5100-9592-5259821.html

The split transaction is as follows:

create or replace type split_tbl as table of varchar2(32767);
/
CREATE OR REPLACE FUNCTION split

 /*
 Simulate the split function in VB
 Author  Strovek
 Date  Jul 29, 2005
 Revised Mar 05, 2007
 Version  1.00.1

 Modification History

*/

(p_list VARCHAR2,   p_del VARCHAR2 := ',') RETURN split_tbl pipelined IS l_idx pls_integer;
l_list VARCHAR2(4000) := p_list;
BEGIN
  LOOP
    l_idx := instr(l_list,   p_del);

    IF l_idx > 0 THEN
      pipe ROW(SUBSTR(l_list,   1,   l_idx -1));
      l_list := SUBSTR(l_list,   l_idx + LENGTH(p_del));
    ELSE
      pipe ROW(l_list);
      EXIT;
    END IF;

  END LOOP;

  RETURN;
END split;

=============================

A sample on how to use the function is as follows:

select * from table(split('one,two,three'));



As a add on to that article, you may want to get the X row, this can be done using the following stored function:

=================================

To get the Xth row from the above:

CREATE OR REPLACE FUNCTION getsplitrow

 /*
  For obtaining the Xth row from the split transaction.  Row starts from 0
  Author Strovek
  Date   Feb 22, 2008
  Revised
*/

(pstr IN VARCHAR2,   prow IN NUMBER,   pdelim IN VARCHAR2 := ',') RETURN VARCHAR2 AS

CURSOR cgetsplit IS
SELECT *
FROM TABLE(CAST(split(pstr,   pdelim) AS
split_tbl));
vgetsplit cgetsplit % rowtype;
vcnt NUMBER;
vrtn VARCHAR2(40);
vval VARCHAR2(40);

BEGIN
  vcnt := 0;
  vrtn := '';

  OPEN cgetsplit;
  LOOP
    FETCH cgetsplit
    INTO vgetsplit;
    EXIT
  WHEN cgetsplit % NOTFOUND;

  IF vcnt = prow THEN
    vrtn := vgetsplit.column_value;
  END IF;

  vcnt := vcnt + 1;
END LOOP;

RETURN vrtn;
END getsplitrow;