Function to Split Multivalued Parameter

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  September 24, 2007 at 7:53 pm

  #178066

  Comments posted to this topic are about the item Function to Split Multivalued Parameter

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers

• philcart

  SSC-Forever

  Points: 47797

  More actions

  October 10, 2007 at 9:50 pm

  #741055

  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

• Steven Willis

  SSCrazy Eights

  Points: 9893

  More actions

  July 16, 2012 at 10:14 am

  #1513197

  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-2 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

  SSC Guru

  Points: 442467

  More actions

  July 16, 2012 at 11:21 am

  #1513222

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

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 10, 2016 at 9:20 am

  #1877843

  Thanks for the script.

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 10, 2016 at 10:32 am

  #1877891

  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