need sql help to split comma separated value into columns

Question

Hey Guys

Not sure how to split these comma separated string into a column. Any help shared will be appreciated

2292,0192,20211018,0778988158494,0000600,150,000000000000001
2292,14642,20210923,630996500903,0000500,061,1
2292,4164,20211021,4006592590505,0002180,150,000000000000001,EUR,21,0000000,501,1,21-01917

Regards
Farhan Jamil

Answer 1

Hi @@Farhan Jamil ,

Please try the following solution. It should work on SQL Server 2012 onwards.
It is based on XML and XQuery.

SQL

-- DDL and sample data population, start  
DECLARE @tbl table (tokens VARCHAR(2000));  
INSERT INTO @tbl  
VALUES('2292,0192,20211018,0778988158494,0000600,150,000000000000001')  
,('2292,14642,20210923,630996500903,0000500,061,1')  
,('2292,4164,20211021,4006592590505,0002180,150,000000000000001,EUR,21,0000000,501,1,21-01917');  
-- DDL and sample data population, end  
       
  
DECLARE @separator CHAR(1) = ',';  
  
SELECT c.value('(/root/r[1]/text())[1]', 'INT') AS col1  
 , c.value('(/root/r[2]/text())[1]', 'VARCHAR(10)') AS col2  
 , c.value('(/root/r[3]/text())[1]', 'DATE') AS col3  
 -- add the rest here  
 , c.value('(/root/r[13]/text())[1]', 'VARCHAR(20)') AS col13  
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
      REPLACE(tokens, @separator, ']]></r><r><![CDATA[') +   
      ']]></r></root>' AS XML)) AS t1(c);  

Answer 2

If your SQL Server is 2016 or higher, you can use the function STRING_SPLIT:

SELECT * FROM STRING_SPLIT('2292,0192,20211018,0778988158494,0000600,150,000000000000001', ',');  

Answer 3

create table #test
(col VARCHAR(2000))
insert into #test
values('2292,0192,20211018,0778988158494,0000600,150,000000000000001')
,('2292,14642,20210923,630996500903,0000500,061,1')
,('2292,4164,20211021,4006592590505,0002180,150,000000000000001,EUR,21,0000000,501,1,21-01917')

;with mycte as (
SELECT col, '["'+ REPLACE(col, ',', '","') + '"] ' jsCol

FROM #test
)
Select col
,max(Case when [key]=0 then value else null end) LVL_1
,max(Case when [key]=1 then value else null end) LVL_2
,max(Case when [key]=2 then value else null end) LVL_3
,max(Case when [key]=3 then value else null end) LVL_4
,max(Case when [key]=4 then value else null end) LVL_5
,max(Case when [key]=5 then value else null end) LVL_6
,max(Case when [key]=6 then value else null end) LVL_7
,max(Case when [key]=7 then value else null end) LVL_8
,max(Case when [key]=8 then value else null end) LVL_9
,max(Case when [key]=9 then value else null end) LVL_10
,max(Case when [key]=10 then value else null end) LVL_11
,max(Case when [key]=11 then value else null end) LVL_12
,max(Case when [key]=12 then value else null end) LVL_13
-- ,max(Case when [key]=13 then value else null end) LVL_14
FROM mycte
cross apply openjson(jsCol ) d
group by col

drop table #test

Answer 4

Hi，@Farhan Jamil

Welcome to Microsoft T-SQL Q&A Forum!

If you don't know json, maybe cte is your best choice.Please try this：

  create table #test  
 (Id INT IDENTITY(1,1),col VARCHAR(2000))  
 insert into #test  
 values('2292,0192,20211018,0778988158494,0000600,150,000000000000001')  
 ,('2292,14642,20210923,630996500903,0000500,061,1')  
 ,('2292,4164,20211021,4006592590505,0002180,150,000000000000001,EUR,21,0000000,501,1,21-01917')  
 select * from #test  
 SELECT col,  
ISNULL([phn1],'') AS [phn1],   
ISNULL([phn2],'') AS [phn2],   
ISNULL([phn3],'') AS [phn3],   
ISNULL([phn4],'') AS [phn4],  
ISNULL([phn5],'') AS [phn5],  
ISNULL([phn6],'') AS [phn6],  
ISNULL([phn7],'') AS [phn7],  
ISNULL([phn8],'') AS [phn8],  
ISNULL([phn9],'') AS [phn9],  
ISNULL([phn10],'') AS [phn10],  
ISNULL([phn11],'') AS [phn11],  
ISNULL([phn12],'') AS [phn12],  
ISNULL([phn13],'') AS [phn13]  
FROM (   
 SELECT Id,   
col,   
 'Phn'+ CAST(ROW_NUMBER()OVER(PARTITION BY Id ORDER BY Id) AS VARCHAR) AS rn,   
 Split.value   
 FROM #test    
 CROSS APPLY String_split(col,',') AS Split )  as table1  
Pivot (Max(Value) FOR rn IN ([phn1],[phn2],[phn3],[phn4],[phn5], [phn6], [phn7],[phn8],[phn9],  [phn10], [phn11],  [phn12], [phn13])  
) AS Pvt  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

HI @Bert Zhou-msft /All

Further to my comment this is the sql query i edited and created . This works perfectly on server 1 but this doesnt work when i am calling the same query from linked server. Unfortunately for ssrs reproting purposes i have to use linked server as i have no choice.

create table #tbl
(
id int identity(1,1),
errordata varchar(max)
,starttime datetime
)

insert into #tbl
select errordata,StartTime from server1.dbo.ProcessErrorData a inner join server1.dbo.ProcessLog b on a.fkBatchProcessLogID = b.pkBatchProcessLogID
where ErrorDescription='Invalid Item Code.'

select * into #tbl2
from
(

SELECT errordata,starttime,
ISNULL([phn1],'') AS [phn1],
ISNULL([phn2],'') AS [phn2],
ISNULL([phn3],'') AS [phn3],
ISNULL([phn4],'') AS [phn4],
ISNULL([phn5],'') AS [phn5],
ISNULL([phn6],'') AS [phn6],
ISNULL([phn7],'') AS [phn7],
ISNULL([phn8],'') AS [phn8],
ISNULL([phn9],'') AS [phn9],
ISNULL([phn10],'') AS [phn10],
ISNULL([phn11],'') AS [phn11],
ISNULL([phn12],'') AS [phn12],
ISNULL([phn13],'') AS [phn13]
FROM (
SELECT Id, starttime,
errordata,
'Phn'+ CAST(ROW_NUMBER()OVER(PARTITION BY Id ORDER BY Id) AS VARCHAR) AS rn,
Split.value
FROM #tbl
CROSS APPLY String_split(errordata,',') AS Split ) as table1
Pivot (Max(Value) FOR rn IN ([phn1],[phn2],[phn3],[phn4],[phn5], [phn6], [phn7],[phn8],[phn9], [phn10], [phn11], [phn12], [phn13])
) AS Pvt
)a

select BranchCode,Barcode, case when phn6=150 then (phn5/100) else -(phn5/100) end as [Value], CAST(phn7 as int)as Units, [Transaction Date], CAST(starttime as date) as FileLoadDate
from
(
select phn2 as BranchCode, cast(phn3 as date) as [Transaction Date], phn4 as Barcode, CAST(phn5 as int) as phn5, phn6, RTRIM(phn7) as phn7, starttime
from #tbl2
)a
order by [Transaction Date] asc

drop table #tbl
drop table #tbl2