CODEWITHSUNDEEP
sqldatabasesql-server-2005
Muhammad Jawad
Muhammad Jawad

Reputation: 122

insert SQL rows from a string with comma record-delimiter and colon-delimited name-value pairs

The data we receive is formatted like this:

'1:0,2:1,3:1,4:0'

The values are separated by commas: the value before the colon is a studentId, and after is a bit value.

I want to store these values in a temp table:

studentID   |   BitValue
1           |    0
2           |    1
3           |    1
4           |    0

How do I do this in SQL Server 2005?

Upvotes: 0

Views: 1480

Answers (2)

bvr
bvr

Reputation: 4826

Try this

DECLARE @param NVARCHAR(MAX)

SET @param = '1:0,2:1,3:1,4:0'

;WITH Split_Col 
AS
(
    SELECT CONVERT(XML,'<table><col>' + REPLACE(ColName,':', '</col><col>') + '</col></table>') AS xmlcol
    FROM
    (
        SELECT Split.a.value('.', 'VARCHAR(100)') AS ColName  
        FROM  
        (
             SELECT CAST ('<M>' + REPLACE(ColName, ',', '</M><M>') + '</M>' AS XML) AS ColName  
             FROM  (SELECT @param AS ColName) TableName
         ) AS A CROSS APPLY ColName.nodes ('/M') AS Split(a)
    ) TableName
)

 SELECT      
 xmlcol.value('/table[1]/col[1]','varchar(100)') AS studentID,    
 xmlcol.value('/table[1]/col[2]','varchar(100)') AS BitValue
 FROM Split_Col

EDIT

Assuming your Student Table has columns StudentId, Name. Find the updated query joining student table

SELECT ST.Name,SP.studentID,SP.BitValue FROM 
(
  SELECT   
 xmlcol.value('/table[1]/col[1]','varchar(100)') AS studentID,    
 xmlcol.value('/table[1]/col[2]','varchar(100)') AS BitValue
 FROM Split_Col
) SP
INNER JOIN Student ST on SP.studentID = ST.studentID

Upvotes: 1

TechDo
TechDo

Reputation: 18639

This works in MS SQL Server 2008.

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
drop table #TempTable

create table #TempTable (studentID int, BitValue int)
declare @var nvarchar(max)
set @var='1:0,2:1,3:1,4:0'

set @var='insert into #TempTable values ('+REPLACE(REPLACE(@var,',','),('), ':', ',')+')'
exec (@var)

select * from #TempTable

drop table #TempTable

for MS SQL Server 2005, try:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
drop table #TempTable

create table #TempTable (studentID int, BitValue int)
declare @var nvarchar(max)
set @var='1:0,2:1,3:1,4:0'

--set @var=REPLACE(@var,',','),(')
set @var='insert into #TempTable values ('+REPLACE(REPLACE(@var,',','); insert into #TempTable values('), ':', ',')+')'
exec (@var)

select * from #TempTable

drop table #TempTable

Upvotes: 2

Related Questions