question

FarhanJamil-5363 avatar image
0 Votes"
FarhanJamil-5363 asked YitzhakKhabinsky-0887 edited

need sql help to split comma separated value into columns

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
2 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @@FarhanJamil-5363,

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);
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 commented

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', ',');

201064-image.png



image.png (3.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi

Thanks for a quick reply.However i need this in a separate column like

ColA ColB ColC

2292 0192 20211018 and so on

Apologies if i was not clear on the first place


REgards

Farhan Jamil

0 Votes 0 ·

Based on your sample data, the column numbers are different for each string?

0 Votes 0 ·
JingyangLi avatar image
0 Votes"
JingyangLi answered
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered FarhanJamil-5363 commented

Hi,@FarhanJamil-5363

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BertZhoumsft-7490

Thanks for your help. However this solution doesn't work in linked server. Is their any work around

201503-image.png



Regards
Farhan Jamil

0 Votes 0 ·
image.png (22.8 KiB)
FarhanJamil-5363 avatar image
0 Votes"
FarhanJamil-5363 answered YitzhakKhabinsky-0887 edited

HI @BertZhoumsft-7490/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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@FarhanJamil-5363,

It seems that you missed my comments under your original question.
Please provide #4, i.e. your SQL Server version by issuing the following T-SQL:

 SELECT @@VERSION;
0 Votes 0 ·
FarhanJamil-5363 avatar image FarhanJamil-5363 YitzhakKhabinsky-0887 ·

Hi YitzhakKhabinsky-0887
Apologies i missed your comment

201505-image.png


201506-image.png




REgards
Farhan Jamil

0 Votes 0 ·
image.png (14.5 KiB)
image.png (7.2 KiB)

@FarhanJamil-5363,

You need to check SQL Server version on the source of data SQL Server.
The same instance that you are querying via linked server.
I am afraid it is below 2016.

0 Votes 0 ·