asked
csv data to separate columns
id data --- ----- 1 a,b,c 2 d,e,f,g,h 3 i,j,k desired output id data_1 data_2 data_3 data_4 -- ------ ------ ------ ----- 1 a b c null 2 d e f g,h 3 i j k null
create table #test (id int, data varchar(10)) insert into #test values (1,'a,b,c'), (2,'d,e,f,g,h'), (3,'i,j,k') select ParsedData.* from #test mt cross apply ( select str = mt.data + ',,' ) f1 cross apply ( select p1 = charindex( ',', str ) ) ap1 cross apply ( select p2 = charindex( ',', str, p1 + 1 ) ) ap2 cross apply ( select p3 = charindex( ',', str, p1 + 2 ) ) ap3 cross apply ( select p4 = charindex( ',', str, p1 + 3 ) ) ap4 cross apply ( select ID, Data1 = substring( str, 1, p1-1 ), Data2 = substring( str, p1+1, p2-p1-1 ), Data3 = substring( str, p1+3, p3-p1-1 ), Data4 = NULLIF(substring( str, p1+5, p4-p1-1 ),',') ) ParsedData
18 People are following this question.
