Function to Split Multivalued Parameter
-
Comments posted to this topic are about the item Function to Split Multivalued Parameter
-
A much more efficient version is available here,
http://philcart.blogspot.com/2007/06/split-function.html
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.
--------------------
Colt 45 - the original point and click interface
-
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.
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
*/
END
GO
-
And you will find a better one here: http://www.sqlservercentral.com/articles/Tally+Table/72993/.
-
Thanks for the script.
-
Iwas Bornready (5/10/2016)
Thanks for the script.Instead of posting replies with no substance to old threads, try taking the time to read the article that Lynn referenced back in 2012.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply