There are many ways by which we can SPLIT delimited string in SQL Server. Few years back I have already done couple of posts regarding this. Let me link those here for quick reference:
1. SPLIT function in SQL Server - Method 1
2. SPLIT function in SQL Server - Method 2
In SQL Server 2005 there are interesting XML enhancements done. So for past few years I have been using this XML trick to parse the comma separated strings or any delimited strings into a table.
CREATE FUNCTION [dbo].[SplitUsingXML]
(
@String VARCHAR(MAX),
--If your delimiter is multi character
--then change it as VARCHAR with appropriate length
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
(
parsedValue VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xml XML
--Below line seems to get screwed up once i post it in Blogger :(
--please use the line as shown in the attached image. Sorry about that.
SET @XML = N'' + REPLACE(@String, @Delimiter, '
') + '
--If your individual value length within the CSV can be more than 25 characters
--then you might want to increase it in the below statement
--pls note it won't throw an error if its more than 25 characters
--just that it would truncate and show only the first 25 character :)
INSERT INTO @Results(parsedValue)
SELECT row.value('.','VARCHAR(25)') as parsedValue
FROM @xml.nodes('//Content/row') AS RECORDS(row)
RETURN
END
GO
Testing:
SELECT parsedValue FROM [dbo].[SplitUsingXML] ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',')
GO
1. SPLIT function in SQL Server - Method 1
2. SPLIT function in SQL Server - Method 2
In SQL Server 2005 there are interesting XML enhancements done. So for past few years I have been using this XML trick to parse the comma separated strings or any delimited strings into a table.
CREATE FUNCTION [dbo].[SplitUsingXML]
(
@String VARCHAR(MAX),
--If your delimiter is multi character
--then change it as VARCHAR with appropriate length
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
(
parsedValue VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xml XML
--Below line seems to get screwed up once i post it in Blogger :(
--please use the line as shown in the attached image. Sorry about that.
SET @XML = N'
--If your individual value length within the CSV can be more than 25 characters
--then you might want to increase it in the below statement
--pls note it won't throw an error if its more than 25 characters
--just that it would truncate and show only the first 25 character :)
INSERT INTO @Results(parsedValue)
SELECT row.value('.','VARCHAR(25)') as parsedValue
FROM @xml.nodes('//Content/row') AS RECORDS(row)
RETURN
END
GO
Testing:
SELECT parsedValue FROM [dbo].[SplitUsingXML] ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',')
GO
Comments
Sorry about the confusion. I have now provided the screenshot of the script as well for your reference.