Tuesday, 13 January 2015

Seperate comma seperated value and store in temp data table

If you want to seperate values by comma or something here is the solution.


DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = 'abc-abc-abc'
set @delimiter = '-'
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

declare @temp table (num nvarchar(max))
insert into @temp
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

select * from @temp

No comments:

Post a comment