All Forums Database
samit_9999 37 posts Joined 03/06
15 May 2008
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 return600-1300-2500-5600-7Please helpThanks,Sam

foxbat 27 posts Joined 06/07
16 May 2008

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 106 posts Joined 02/07
16 May 2008

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_dataunion allselect substring(rest from 1 for position(',' in rest)-1), substring(rest from position(',' in rest)+1 for char(rest))from temp_tablewhere char(rest) > 1)select subpart from temp_table; *** Query completed. 4 rows found. One column returned. *** Total elapsed time was 1 second.subpart------------------------------600-1300-2500-5600-7

samit_9999 37 posts Joined 03/06
19 May 2008

Thank You rgs!

samit_9999 37 posts Joined 03/06
19 May 2008

rgs ,Can i pass the result to a another query. I do not want to use BTEQ.Any options.Thanks,Sam

rgs 106 posts Joined 02/07
19 May 2008

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.

You must sign in to leave a comment.