Monday, October 03, 2011

How to SPLIT a delimited string values in SQL Server 2005

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

2 comments:

Vadivel said...

Seems the script i posted gets messed up in Blogger! All texts within lesser than and greater than has vanished in the line where i set @XML variable.

Sorry about the confusion. I have now provided the screenshot of the script as well for your reference.

Kreative Mart said...

You can better use the html codes for the angle brackets like for > you could use > That way you will be able to display the xml tags without compromising the display.