Split and concatenate function

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  November 13, 2013 at 1:35 pm

  #285168

  Comments posted to this topic are about the item Split and concatenate function

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• ui ranejeb

  SSC Enthusiast

  Points: 168

  More actions

  November 26, 2013 at 8:00 am

  #1669165

  My favorite one:

  http://sqlhint.com/sqlserver/sqlserver/how-to/best-split-function-tsql-delimited

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 31, 2014 at 10:23 am

  #1685140

  The best splitter for T-SQL is right here[/url] along with several tests. It will outperform any splitter using while loops or xml.

  With it, you can replace your function to this. Note that it has a limit to 4000 unicode chars (8000 for non-unicode) to avoid the use of large data types and improve performance.

  -- Split and concatenate back to string

  CREATE FUNCTION [dbo].[Split_and_concatenate]

  (

  @str NVARCHAR(4000)

  ,@delimiter CHAR(1)

  )

  RETURNS NVARCHAR(8000)

  AS

  BEGIN

  SELECT STUFF( (SELECT '; ' + CASE Item WHEN '1' THEN '11'

  WHEN '2' THEN '22'

  ELSE '33' END

  FROM dbo.[DelimitedSplitN4K](@str, @delimiter)

  FOR XML PATH('')),1,2, '')

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2