Home Split files using SQL
Reply: 1

Split files using SQL

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)
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
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 
       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('<m>'+replace(STEPID1, ';', '</m><m>')+'</m>' as xml) [STEPID1]  from <table>
) 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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.330812 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO