Re: splitting a string into columns

From: Walt <>
Date: Fri, 02 May 2008 09:55:46 -0400
Message-ID: <D3FSj.61077$>

kes wrote:
> I'm working with Oracle 10g R2
> create table table1(
> table1_id number,
> csv1 varchar2(100),
> csv2 varchar2(100));
> insert into table1 values(1, 'a,b,c', 'w,x,y,z');
> insert into table1 values(2, 'a,b,c,d', 'x,y,z');
> select * from table1;
> "TABLE1_ID","CSV1","CSV2"
> "1","a,b,c","w,x,y,z"
> "2","a,b,c,d","x,y,z"
> I would like to see it like
> 1, a, b, c, , w, x, y, z
> 2, a, b, c, d, , x, y z
> Should I create a store procedure that will split the strings into
> different rows, then pivot them? Are there any good articles to point
> to a better way? Any suggestions will be welcomed.

I'm not sure what you're trying to accomplish here, but did you try the concatenation operator ?

Select table1_id||csv1||csv2 from table1;

Generally, I wouldn't store comma separated values in the database, I'd store the values individually and concatenate them into a csv list as needed.

//Walt Received on Fri May 02 2008 - 08:55:46 CDT

Original text of this message