Splitting Comma Seperated String to Rows

Got the following email from one of my users:

I am trying to write a SQL select to get comma delimited values from one field in rows.

For example I have a Table T1
create table t1(a  varchar2(30),
b  varchar2(30));

insert into t1 values ('A','27.68%,2.78%,69.55%');

SELECT A, B FROM T1;
A       B
=====  =====
A  27.68%,2.78%,69.55%

Can you help me write a SQL which will give the following output?

A        PERCENT
======  ========
A   27.68%
A    2.78%
A   69.55%

My solution is rather ugly, but it works (on 10g and above) and makes no assumptions about the number of elements in the list :

With t2 as (select A,','||B||',' as B from t1)
SELECT A,SUBSTR(B,INSTR(B,',',1,LEVEL)+1,INSTR(B,',',1,LEVEL+1)-INSTR(B,',',1,LEVEL)-1)
FROM t2
CONNECT BY LEVEL <= length(regexp_replace('[^\,]',''))-2 ;

Senior DBA had a simpler solution: “Just replace all the commas with new lines”.

Advertisements

10 Comments on “Splitting Comma Seperated String to Rows”

  1. prodlife says:

    I apologize in advance for using that old illegal “connect by” trick. Unsupported and all. Not recommended. Don’t try it on production.

  2. prodlife says:

    You can remove the “connect by” if you join on (select rownum from dba_objects) or something similar.

  3. create table t1(a varchar2(30), b varchar2(30));
    insert into t1 values (‘A’,’27.68,2.78,69.55′);
    insert into t1 values ('B','1,2,3');
    select a,column_value from t1,xmltable(b);

    select a,xmlcast(column_value as number) b from t1,xmltable(b);
    A B
    --- ----------
    A 27.68
    A 2.78
    A 69.55
    A 27.68
    A 2.78
    A 69.55
    B 1
    B 2
    B 3

  4. MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM, I forget to write you need 11g

  5. Rudy says:

    1) Bad design OR let the app do it.

    2) This is even worse:

    SQL> insert into t1 values (‘A2’, ‘12.4%,66%,99.1%,45.33%’);

    SQL> commit;

    Commit complete.

    SQL> select a, regexp_substr(b, ‘[^%]+’, 1, ao.rn) from t1
    join
    (select rownum rn from all_objects where rownum < 100) ao
    on
    rn <= length(regexp_replace(b, ‘[^,]’,”))+1;

    A REGEXP_SUBSTR(B,'[^%]+’,1,AO.RN)
    —————————— ————————————————————————————————————————
    A2 ,45.33
    A2 ,99.1
    A2 ,66
    A2 12.4
    A ,69.55
    A ,2.78
    A 27.68

    7 rows selected.

    Note: 100 could be the max number of commas.

  6. prodlife says:

    Laurent,
    Cute XML solution! You forgot to mention that the % character is not supported.

  7. prodlife says:

    Rudy,

    The design is terrible, this goes without saying. Everyone knows this is bad design and yet I run into csv strings in different apps every few month.

    I’m not sure that letting the app do this is an improvement.

    Using regexp_substr is much clearer than substr-instr! Why didn’t I think of it?

  8. Radian says:

    Hello.
    There is also another solution:

    create or replace type myTableType as table
    of varchar2 (255);
    /

    create or replace
    function in_list( p_string in varchar2 ) return myTableType
    as
    l_string long default p_string || ‘,’;
    l_data myTableType := myTableType();
    n number;
    begin
    loop
    exit when l_string is null;
    n := instr( l_string, ‘,’ );
    l_data.extend;
    l_data(l_data.count) :=
    ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
    l_string := substr( l_string, n+1 );
    end loop;

    return l_data;
    end;
    /

    Then you can do the following:
    SELECT * FROM TABLE(in_List(‘1,2,3,4,5’))

    Now, related to the part of generating 100 rows, I also have an alternative solution:

    SELECT ROWNUM FROM dual
    CONNECT BY 1=1
    AND ROWNUM < 100

    I am not sure if these are very good for your case, but I just discovered your blog and wanted to leave a reply 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s