Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Function to Split Multivalued Parameter


Function to Split Multivalued Parameter

Author
Message
Hari.Sharma
Hari.Sharma
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1332 Visits: 455
Comments posted to this topic are about the item Function to Split Multivalued Parameter

Cheers,
Hari
Tips & Tricks for SQL BI Developers

philcart
philcart
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2748 Visits: 1434
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.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1721
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


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
And you will find a better one here: http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8682 Visits: 885
Thanks for the script.
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10286 Visits: 9585
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search