splitting a string into columns

#1

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.

Alex

#2

kes wrote:

Quote:

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.

Alex

Why does this require a stored procedure and where's the pivot?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

kes wrote:

Quote:

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.

Alex

Why does this require a stored procedure and where's the pivot?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

kes wrote:

Quote:

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.

Alex

Why does this require a stored procedure and where's the pivot?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

kes wrote:

Quote:

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.

Alex

Why does this require a stored procedure and where's the pivot?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

On May 1, 7:55 pm, kes <abi... (AT) gmail (DOT) com> wrote:

Quote:

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.

Alex

It looks like all you are asking for is to change the column separator
Check into the SET command if that is the case.
Ed

#7

On May 1, 7:55 pm, kes <abi... (AT) gmail (DOT) com> wrote:

Quote:

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.

Alex

It looks like all you are asking for is to change the column separator
Check into the SET command if that is the case.
Ed

#8

On May 1, 7:55 pm, kes <abi... (AT) gmail (DOT) com> wrote:

Quote:

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.

Alex

It looks like all you are asking for is to change the column separator
Check into the SET command if that is the case.
Ed

#9

On May 1, 7:55 pm, kes <abi... (AT) gmail (DOT) com> wrote:

Quote:

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.

Alex

It looks like all you are asking for is to change the column separator
Check into the SET command if that is the case.
Ed

#10

Quote:

Alex

Why does this require a stored procedure and where's the pivot?
--
Thank you for following up.

The csv1 and csv2 columns are a format for RNA sequences that have
been modified. This is why these CSV values are stored in the
database, it's usually been sufficient for our needs over the past 2
years. Now people want to see the different individual values.

e.g., xA,yG,U,G,xG

would be stored as value csv1, now people would like to see, is there
a correlation between U in the third location and this RNA sequence's
efficacy.

the reason for the pivot is I could use level in a select statement to
select
1, xA
2, yG
3, U
4, G
5, xG

then pivot it to
id, csv1_1, csv1_2, csv1_3, csv1_4, csv1_5
3, xA, yG, U, G, xG

using dynamic SQL and decode.

I was just hoping for a more elegant solution.