Home Split files using SQL

Split files using SQL

okla
1#
okla Published in 2017-12-05 13:55:32Z
 This is my first time posting a question, so please bear with me for any errors. I am trying to Split the numeric values with a delimiter of ';' using sql into separate columns, i was able to achieve splitting part of it using the code below but i will like to split the rest of it within column 3 to separate columns I have a table like this ID catalogNumber STEPID1 ---------------------------- 815326 FBRS415 499023;499027;726203  with tmp(catalognumber,stepid1_split,stepid1) as ( select catalognumber,cast(LEFT(stepid1, CHARINDEX(';',stepid1+';')-1) as varchar),cast(STUFF(stepid1, 1, CHARINDEX(';',stepid1+';'), '') as varchar) from EXACT_MATCH union all select catalognumber, cast(LEFT(stepid1, CHARINDEX(';',stepid1+';')-1) as varchar), cast(STUFF(stepid1, 1, CHARINDEX(';',stepid1+';'), '') as varchar) from tmp where stepid1> '' ) select * from tmp  ID catalogNumber STEPID1 STEPID2 STEPID3 --------------------------------------------------- 815326 FBRS415 499023 499027 726203 
Yogesh Sharma
2#
Yogesh Sharma Reply to 2017-12-06 06:14:12Z
 For that, you could try XML.. .node() method to split your comma values into individual columns note that this will always gives you three columns. You will need to add some extra columns in order to display comma separated values as you define select distinct a.ID, a.catalogNumber, c.value('/m[1]', 'varchar(max)') [STEPID1], c.value('/m[2]', 'varchar(max)') [STEPID2], c.value('/m[3]', 'varchar(max)') [STEPID3] from ( select ID , catalogNumber , cast(''+replace(STEPID1, ';', '')+'' as xml) [STEPID1] from ) a cross apply STEPID1.nodes ('/m') as STEPID1(c)  Result : ID catalogNumber STEPID1 STEPID2 STEPID3 815326 FBRS415 499023 499027 726203 
 You need to login account before you can post.
Processed in 0.330812 second(s) , Gzip On .