Parsing a row to create multiple rows

Database
Enthusiast

Parsing a row to create multiple rows

Hi,

I have a row which has data in the following format
'600-1,300-2,500-5,600-7'

I need to parse these based on , and created multiple rows. In the above example it should return

600-1
300-2
500-5
600-7

Please help

Thanks,
Sam
5 REPLIES
Enthusiast

Re: Parsing a row to create multiple rows

One way to do it is to retrieve the row into MSWORD and replace all occurences of the comma character with the following text

');^linsert into temptable('

This should give rows in the following format. Which then needs to be manually edited a little to finish your job.

'600-1’);
insert into temptable(‘300-2’);
insert into temptable(‘500-5’);
insert into temptable(‘600-7'
rgs
Enthusiast

Re: Parsing a row to create multiple rows

Use a recusrive query:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from my_data;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

item subpart
----------- ------------------------------
1 600-1,300-2,500-5,600-7

BTEQ -- Enter your DBC/SQL request or BTEQ command:
with recursive temp_table (subpart, rest) as (
select substring(subpart from 1 for position(',' in subpart)-1),
substring(subpart from position(',' in subpart)+1 for char(subpart)) || ','
from my_data
union all
select substring(rest from 1 for position(',' in rest)-1),
substring(rest from position(',' in rest)+1 for char(rest))
from temp_table
where char(rest) > 1)
select subpart from temp_table;

*** Query completed. 4 rows found. One column returned.
*** Total elapsed time was 1 second.

subpart
------------------------------
600-1
300-2
500-5
600-7
Enthusiast

Re: Parsing a row to create multiple rows

Thank You rgs!
Enthusiast

Re: Parsing a row to create multiple rows

rgs ,

Can i pass the result to a another query. I do not want to use BTEQ.

Any options.

Thanks,
Sam
rgs
Enthusiast

Re: Parsing a row to create multiple rows

But of course! This was just a sample showing how to do it. It is just a query and you can make it a sub query of something more complicated or insert the results into another table. That is up to you.