Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function to Split Multivalued Parameter Expand / Collapse
Author
Message
Posted Monday, September 24, 2007 7:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
Comments posted to this topic are about the item Function to Split Multivalued Parameter

Cheers,
Hari
Tips & Tricks for SQL BI Developers
Post #402324
Posted Wednesday, October 10, 2007 9:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:47 PM
Points: 2,693, Visits: 1,210

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
Post #409335
Posted Monday, July 16, 2012 10:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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

Post #1330251
Posted Monday, July 16, 2012 11:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,734, Visits: 32,497
And you will find a better one here: http://www.sqlservercentral.com/articles/Tally+Table/72993/.



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)
Post #1330291
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse