CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

--
PRINT 'Create function KK_FN_DelimSplit_V2'
GO
EXEC dbo.kk_sm_SP_LogScriptRun 'KK_FN_DelimSplit_V2', '040717'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[KK_FN_DelimSplit_V2]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION [dbo].[KK_FN_DelimSplit_V2]
GO

CREATE FUNCTION dbo.KK_FN_DelimSplit_V2
(
	@strSource	varchar(8000), 
	@strDelimiter	varchar(10) = ',',
	@strBlank	varchar(8000) = '[DELETE]'	-- What to return if an item is blank?  (e.g. '', NULL or '[BLANK]')
							-- Use '[DELETE]' to delete any NULL items
)
RETURNS @tblArray TABLE
(
	Item	int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Value	varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
/* WITH ENCRYPTION */
AS
/*
 * KK_FN_DelimSplit_V2	Split a string based on delimiter
 *
 * SELECT * FROM dbo.KK_FN_DelimSplit_V2('A,B,C', ',', NULL)
 *
 * Returns:
 *
 *	Resultset of Value/Value pairs
 *
 * HISTORY:
 *
 * 04-Sep-2003 KBM  V2 - Changed Item/Value to Value/Value.  Fixed bug final value was empty - e.g. "A,B,C," [Value 4 should be blank, not absent]
 * 05-Oct-2003 KBM  Increased size of "VALUE" and other columns
 * 21-Apr-2004 KBM  Converted to use Tally table, for speed, and to fix bug when using SPACE as delimiter
 *		    Based on article: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1092
 *                  Does NOT catch a blank final value
 * 17-Jul-2004 KBM  Fixed "final value blank" bug, based on Joe Celko's article
 *		    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20675
 *		    Does NOT catch single blank values with MULTI-character delimiters
 */
BEGIN
DECLARE @intLen		int,		-- Length of the source string
	@intNewDelimiter int

	IF DATALENGTH(@strDelimiter) > 1
	BEGIN
		-- Cannot safely handle BLANK fields with multi-character delimiter
		-- Use single character delimiter instead
		-- Find lowest character NOT used in @strSource
		SELECT	@intNewDelimiter = MIN(mb_int_ID)
		FROM	dbo.kk_MB_INT_Integer
		WHERE	@strSource NOT LIKE '%' + CHAR(mb_int_ID) + '%'
		SELECT	@strSource = REPLACE(@strSource, @strDelimiter, CHAR(@intNewDelimiter)),
			@strDelimiter = CHAR(@intNewDelimiter)
	END

	SELECT	@intLen = DATALENGTH(@strSource)

	-- Surround string with delimiters to make processing easier
	SELECT	@strSource = @strDelimiter + @strSource + @strDelimiter

	-- Split string using Tally table and Delimiters
	INSERT @tblArray(Value)
	SELECT	
		[Value] = COALESCE(
				NullIf(
					SUBSTRING 
					(
						@strSource, 
						MAX(S1.mb_int_ID + 1), 
						(S2.mb_int_ID - MAX(S1.mb_int_ID + 1))
					),
					 ''), 
				@strBlank)	-- Convert blank items to user-supplied value
	FROM 	dbo.kk_MB_INT_Integer AS S1, 
		dbo.kk_MB_INT_Integer AS S2 
	WHERE	    SUBSTRING (@strSource , S1.mb_int_ID, 1) = @strDelimiter
		AND SUBSTRING (@strSource , S2.mb_int_ID, 1) = @strDelimiter
		AND S1.mb_int_ID < S2.mb_int_ID
		AND S2.mb_int_ID <= DATALENGTH(@strSource) + 1
	GROUP BY S2.mb_int_ID
	HAVING	COALESCE(
			NullIf(
				SUBSTRING 
				(
					@strSource, 
					MAX(S1.mb_int_ID + 1), 
					(S2.mb_int_ID - MAX(S1.mb_int_ID + 1))
				),
				 ''), 
			@strBlank, 'XXX')	-- Need to make NULL fail to match here, rather than "do nothing"!
		    <> '[DELETE]'
	ORDER BY S2.mb_int_ID

	RETURN
/*
-- Example:
DECLARE	@strString varchar(8000)
SELECT	@strString = '1,2,,4'
SELECT	* 
FROM	dbo.KK_FN_DelimSplit_V2(@strString, ',', '[BLANK]')

-- ===== Create Tally Table (Run from here to marker below) ==== --
SET NOCOUNT OFF

CREATE TABLE dbo.kk_MB_INT_Integer
(
	mb_int_ID int NOT NULL,
	CONSTRAINT [PK_kk_MB_INT_Integer] PRIMARY KEY CLUSTERED
	(
		[mb_int_ID]
	) WITH FILLFACTOR = 100 ON [PRIMARY] 
)


DECLARE @v sql_variant 
SET @v = N'List of integers for reference / joins'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'kk_MB_INT_Integer', NULL, NULL
SET @v = N'ID number'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'kk_MB_INT_Integer', N'column', N'mb_int_ID'

-- sp_indexoption cannot be executed within a transaction block
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowrowlocks', TRUE
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowpagelocks', TRUE

SET NOCOUNT ON

TRUNCATE TABLE kk_MB_INT_Integer

DECLARE @I int 
SELECT	@I = 1
WHILE @I <= 8000	-- this value should be the upper limit on the string length
BEGIN
	INSERT	dbo.kk_MB_INT_Integer(mb_int_ID) VALUES (@I) 
	SELECT	@I = @I+1
END
SET NOCOUNT OFF
-- ===== Create Tally Table -- ** RUN TO HERE ** ==== --

*/
END
--================== KK_FN_DelimSplit_V2 ==================--
GO
PRINT 'Create function KK_FN_DelimSplit_V2 - DONE'
GO
--

quote:

---

Originally posted by Kristen

To split a TEXT datatype that contains CSV or other delimited data see

http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx

Kristen

---

-- Based on code by http://www.umachandar.com/technical/SQL70Scripts/Main10.htm
-- Modified by Kristen 2004/2005

DECLARE	@pos		int,
	@numtokens	int,
	@field		varchar(128),
	@field2		varchar(128),
	@strDelimiter	varchar(1),
	@previdx	varchar(255),
	@maxtokens	int


-- Set these parameters as appropriate, and run down to "TEST RIG"
SELECT	@maxtokens = 5,		-- Number of tokens, per row, to be split
	@field = 'MyCol',	-- Name of Column to split
	@strDelimiter = '|'	-- Delimiter - e.g. CHAR(9) or ','

SELECT	@field2 = @field + ' + ''' + @strDelimiter + ''''	-- Shorthand! for @field plus delimiter

-- SELECT stuff
SELECT	@numtokens = 0,	-- Initialisation
	@previdx = '0'	--  charindex('|', @str, 1)
PRINT	'SELECT'
WHILE(@numtokens < @maxtokens)
BEGIN
	PRINT	CASE WHEN @numtokens = 0 THEN '  ' ELSE ', ' END
		+ '[COLUMN_' + CONVERT(varchar(20), @numtokens+1) + '] = '
		+ 'substring(' + @field2 + ', ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END
		+ ' + 1, charindex(''' + @strDelimiter + ''', ' + @field2 + ', '
		+ CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' + 1)'
	        + ' - ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' - 1 )'
	SELECT	@previdx = ' charindex(''' + @strDelimiter + ''', ' + @field2
			+ CASE WHEN @numtokens = 0 THEN '' ELSE ', ' + @previdx + ' + 1' END + ')',
		@numtokens = @numtokens + 1
END
PRINT 'FROM	dbo.MyTable'

-- UPDATE stuff
SELECT	@numtokens = 0	-- Initialisation
PRINT	'UPDATE U'
PRINT	'SET'
WHILE(@numtokens < @maxtokens)
BEGIN
	IF @numtokens = 0
	BEGIN
		PRINT CHAR(9) + '  @I'  + CONVERT(varchar(20), @numtokens+1) 
			+ ' = CHARINDEX(''' + @strDelimiter + ''', ' + @field2 + ')'
		PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1) 
			+ '] = LEFT(' + @field + ', @I'  
			+ CONVERT(varchar(20), @numtokens+1) + '-1)'
	END
	ELSE
	BEGIN
		PRINT CHAR(9) + ', @I'  + CONVERT(varchar(20), @numtokens+1) 
			+ ' =  NullIf(CHARINDEX(''' + @strDelimiter + ''', ' 
			+ @field2 + ', @I'  + CONVERT(varchar(20), @numtokens) 
			+ '+1), 0)'
		PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1) 
			+ '] = SUBSTRING(' + @field + ', @I'  + CONVERT(varchar(20), @numtokens) 
			+ '+1, @I'  + CONVERT(varchar(20), @numtokens+1) + '-@I'  
			+ CONVERT(varchar(20), @numtokens) + '-1)'
	END
	SELECT @numtokens = @numtokens + 1
END
PRINT 'FROM	dbo.MyTable AS U'



-- TEST RIG:
DECLARE @MyTable TABLE
(
	MyCol varchar(50), 
	MyCol1 varchar(10), 
	MyCol2 varchar(10), 
	MyCol3 varchar(10), 
	MyCol4 varchar(10), 
	MyCol5 varchar(10)
)

INSERT INTO @myTable 
SELECT	'AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null UNION ALL
SELECT	'A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null UNION ALL
SELECT	'AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null UNION ALL
SELECT	'AA9|BBBBBBB9|CCCC9|DDD9|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA|BBBBBBBA|CCCCA|DDDA', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAB|BBBBBBBB|CCCCB|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAC|BBBBBBBC|CCCCC', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAD|BBBBBBBD|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAE|BBBBBBBE', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAF|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAG', Null,Null,Null,Null,Null

DECLARE	@I1	int,
	@I2	int,
	@I3	int,
	@I4	int,
	@I5	int

UPDATE U
SET
	  @I1 = CHARINDEX('|', MyCol + '|')
	, [MyCol1] = LEFT(MyCol, @I1-1)
	, @I2 =  NullIf(CHARINDEX('|', MyCol + '|', @I1+1), 0)
	, [MyCol2] = SUBSTRING(MyCol, @I1+1, @I2-@I1-1)
	, @I3 =  NullIf(CHARINDEX('|', MyCol + '|', @I2+1), 0)
	, [MyCol3] = SUBSTRING(MyCol, @I2+1, @I3-@I2-1)
	, @I4 =  NullIf(CHARINDEX('|', MyCol + '|', @I3+1), 0)
	, [MyCol4] = SUBSTRING(MyCol, @I3+1, @I4-@I3-1)
	, @I5 =  NullIf(CHARINDEX('|', MyCol + '|', @I4+1), 0)
	, [MyCol5] = SUBSTRING(MyCol, @I4+1, @I5-@I4-1)
FROM	@MyTable AS U

SELECT	*
FROM	@MyTable

-- SQL1 Cost .0625 (22%)
delete externalgraderesults
  from externalgraderesults r
  join dbo.udf_SplitUnlim(@externalresultIdList,',') idlist on r.externalgraderesult_id=idlist.value

-- SQL2 Cost .207 (73.25%)
  delete externalgraderesults
  from externalgraderesults r
  join dbo.udf_Split(@externalresultIdList,',') idlist on r.externalgraderesult_id=idlist.value

-- SQL3 Cost .0133 (4.7%)
  delete 
  from externalgraderesults 
  where externalgraderesult_id in (18,122,131)

-- 7998 bytes max (but faster)
   CREATE FUNCTION dbo.udf_Split (@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(',' + @param + ',', Number + 1,
                    charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(',' + @param + ',') - 1
            AND  substring(',' + @param + ',', Number, 1) = ',')
 
 
-- Unlimited size
   CREATE FUNCTION dbo.udf_SplitUnlim(@list  ntext,
                                     @delim nchar(1) = N',')
   RETURNS @t TABLE (str varchar(4000),
                     nstr nvarchar(2000)) AS
   BEGIN
      DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
      DECLARE @slice nvarchar(4000),
              @textpos int,
              @maxlen int,
              @stoppos int
 
      SELECT @textpos = 1, @maxlen = 4000 - 2
      WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
      BEGIN
         SELECT @slice = substring(@list, @textpos, @maxlen)
         SELECT @stoppos = @maxlen - charindex(@delim, reverse(@slice))
         INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
         SELECT @textpos = @textpos - 1 + @stoppos + 2   -- On the other side of the comma.
      END
      INSERT @slices (slice)
          VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)
 
      INSERT @t (str, nstr)
         SELECT str, str
         FROM   (SELECT str = ltrim(rtrim(substring(s.slice, N.Number + 1,
                        charindex(@delim, s.slice, N.Number + 1) - N.Number - 1)))
                 FROM  Numbers N
                 JOIN  @slices s ON N.Number <= len(s.slice) - 1
                                AND substring(s.slice, N.Number, 1) = @delim) AS x
 
      RETURN
   END

quote:

---

Originally posted by nr

quote:

---

Originally posted by Kristen

To split a TEXT datatype that contains CSV or other delimited data see

http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx

Kristen

---

Don't know if that's the thread I was involved in before (or if it copes with this) but charindex doesn't support text datatypes so it will convert the value to a string and could miss values.

---

ALTER FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO

-- Create temp table to test inserting values into
create table #t (num int)

-- Create a comma delimited string to test with
declare @str	varchar(500)
select @str = '4,2,7,7834,45,24,45,77'

--------------------------------------------------------
---- Code to load the delimited string into a table ----
--------------------------------------------------------

-- Create insert for comma delimited values
declare @sql varchar(8000)
select @sql = 'insert into #t select '+
	replace(@str,',',' union all select ')

-- Load values from comma delimited string into a table
exec ( @SQL )

--------------------------------------------------------
--------------------------------------------------------


-- Select values from temp table to show results
select * from #t

if object_id('tempdb..#t') is not null
	begin drop table #t end

quote:

---

...With up to 50 items, the elapsed time to load the string into the table was less than 0.003 seconds. With 250 items in the list, it took 0.110 second...

---

DECLARE @List VARCHAR(8000)

select
    @list = coalesce(@list+',','')+ char(round((rand(convert(binary(4),newid())) * 25),0)+97)
from
    dbo.numbers
where
    n < 3999

select 1 + len(@list) - len(replace(@list, ',', '')) 'items in list', len(@list) 'string length'

declare @t table (r varchar(6))

declare @start datetime

select @start = getdate()

insert @t
SELECT SUBSTRING(',' + @List + ',', w.i + 1, CHARINDEX(',', ',' + @List + ',', w.i + 1) - w.i - 1) value
FROM (
SELECT v0.n + v1.n + v2.n + v3.n i
FROM (
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i < LEN(',' + @List)
select datediff(ms, @start, getdate()) milliseconds
delete @t
select @start = getdate()
insert @t
select s from dbo.Split(',',@list)
option(MAXRECURSION 4000)
select datediff(ms, @start, getdate()) milliseconds

quote:

---

Originally posted by Arnold Fribble

Using CROSS APPLY with a split function in SQL Server 2005, I was amazed at the difference in speed between inline TVFs and multiline TVFs.
So here's a split function using a recursive CTE, which is both inline and doesn't require a tally table:

ALTER FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO

---

DECLARE @List VARCHAR(8000)

SELECT @List = '48140,20657,58498,'

select 1 + len(@list) - len(replace(@list, ',', '')) 'items in list', len(@list) 'string length'

declare @t table (r varchar(6))

insert @t
SELECT SUBSTRING(',' + @List + ',', w.i + 1, CHARINDEX(',', ',' + @List + ',', w.i + 1) - w.i - 1) value
FROM (
SELECT v0.n + v1.n + v2.n + v3.n i
FROM (
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 
UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 
UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 
UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024 
UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304 
UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 
UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i < LEN(',' + @List)

SELECT	* FROM @t

WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i <= LEN(',' + @List)