SQLServerCentral / Article Discussions / Discuss content posted by ~~Hari.Bagra / Article Discussions by Author / Function to Split Multivalued Parameter / Latest Posts

RE: Function to Split Multivalued Parameter

Lynn Pettis — Mon, 16 Jul 2012 11:21:14 GMT

And you will find a better one here: [b][url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url][/b].

RE: Function to Split Multivalued Parameter

Steven Willis — Mon, 16 Jul 2012 10:14:25 GMT

Here's a FAST function that avoids using a loop and uses a clustered index seek on the output table by adding a primary key and using that in the WHERE clause. I did not develop the idea to use the XML split myself...I found it some time ago from a Google search and can't give a proper reference to the developer. If anyone knows who came up with it and can give proper credit, please do.[code]CREATE FUNCTION dbo.tvfParseDelimitedString ( @S NVARCHAR(MAX) -- Delimited input string ,@Split CHAR(1) -- Delimiter used for the input string )RETURNS @Table TABLE ( [ID] INT NOT NULL IDENTITY(1,1) ,[Value] NVARCHAR(MAX) NULL ,PRIMARY KEY ([ID]) ,UNIQUE ([ID]) )BEGIN DECLARE @X XML SET @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>') INSERT INTO @Table SELECT T.c.value('.','NVARCHAR(MAX)') AS [Value] FROM @X.nodes('/root/s') T (c) RETURN /* SELECT [Value] FROM dbo.tvfParseDelimitedString(N'1,AAA,4,BB,777,XYZ',',') WHERE [ID] > 0 */ENDGO[/code]

RE: Function to Split Multivalued Parameter

philcart — Wed, 10 Oct 2007 21:50:08 GMT

A much more efficient version is available here,[url=http://philcart.blogspot.com/2007/06/split-function.html]http://philcart.blogspot.com/2007/06/split-function.html[/url]It uses varchar(max), so you're not limited in how many values are passed. Doesn't use a loop, so the number of values passed doesn't affect performance.

Function to Split Multivalued Parameter

Hari.Sharma — Mon, 24 Sep 2007 19:53:44 GMT

Comments posted to this topic are about the item [B]Function to Split Multivalued Parameter[/B]