String Split

On occasion we need split and treat a single comma-delimited value as if it were a column in a table. There are two methods to achieve this.

1.TABLE SQL function

We can take advantage of the TABLE SQL function and PL/SQL function to do this.

Example:

CREATE OR REPLACE TYPE GTYP_STR_TABLE IS TABLE OF VARCHAR2 (32767);

/

CREATE OR REPLACE FUNCTION split(pv_list VARCHAR2, pv_del VARCHAR2 := ',')

  RETURN gtyp_str_table

  PIPELINED IS

  lv_idx  PLS_INTEGER;

  lv_list VARCHAR2(32767) := pv_list;

 

  lv_value VARCHAR2(32767);

BEGIN

  LOOP

    lv_idx := instr(lv_list, pv_del);

    IF lv_idx > 0

    THEN

      PIPE ROW(substr(lv_list, 1, lv_idx - 1));

      lv_list := substr(lv_list, lv_idx + length(pv_del));

   

    ELSE

      PIPE ROW(lv_list);

      EXIT;

    END IF;

  END LOOP;

  RETURN;

END split;

/

With this function, we can run a query like this:

select * from table(split('a,b,c'));

2. Regular expression function

On occasion that customized functions can’t be used, we can use regexp_substr to archive this.

Example:

The example table description as below:

SQL> desc table2;

Name      Type          Nullable Default Comments

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

ID        NUMBER(20)    Y                        

NAMES VARCHAR2(100) Y                         

 

SQL> select * from table2;

 

                   ID NAMES

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

                    1 name1,name2

                    2 name2,name3

 

String split Sql is:

 

SQL> SELECT NAME, id

      FROM (SELECT regexp_substr(t2.names, '[^,]+', 1, LEVEL) NAME,

                   LEVEL lv,

                   lag(LEVEL, 1, 0) over(PARTITION BY t2.id ORDER BY LEVEL) lg,

                   t2.id

              FROM table2 t2

            CONNECT BY regexp_substr(t2.names, '[^,]+', 1, LEVEL) IS NOT NULL)

     WHERE lv != lg;

 

The result is:

NAME      ID

---------

name1     1

name2     1

name2     2

name3     2

reference:
http://articles.techrepublic.com.com/5100-10878_11-5259821.html
http://www.oracle.com/technology/oramag/code/tips2007/070907.html