As requested, Jeff:
USE DBA
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitN]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_SplitN]
GO
CREATE FUNCTION [dbo].[udf_SplitN]
( @inString nvarchar(MAX)
, @delim nvarchar(255)
)
RETURNS @arrSplit TABLE
( IDX int IDENTITY(0, 1) PRIMARY KEY
, [value] nvarchar(1024)
)
AS
/* =============================================================================
** original idea by Anith Sen, posted on http://www.simple-talk.com at
** http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/
** Description: Split a nvarchar string into chunks using a delimiter of up to
** 255 characters.
**
** Notes: See the corresponding udf_Split for the varchar version.
**
SELECT *
FROM dbo.udf_Split('one,two,three,four,five,six,seven,eight,nine,ten', ',')
SELECT *
FROM dbo.udf_Split('Monday--Tuesday--Wednesday--thursday--friday--saturday--sunday', '--')
========================================================================== */
BEGIN
INSERT INTO @arrSplit
( [value]
)
SELECT SUBSTRING(@inString + @delim, number, CHARINDEX(@delim, @inString + @delim, number) - number)
FROM dbo.Numbers
WHERE number <= LEN(REPLACE(@inString, N' ', N'`'))
AND SUBSTRING(@delim + @inString, number, LEN(REPLACE(@delim, N' ', N'`'))) = @delim
ORDER BY number
RETURN
END
GOThis is used in preference to the function below in cases where 4000 characters is likely to be exceeded, and sometimes by MB. The msTVF works very well at splitting XML files that contain multiple documents - a problem a third-party vendor presented that could not be changed due to their third party software. That's the primary reason for the msTVF's existence in this form.
When I know the delimited varchar data are short, I'll use the much-discussed and augmented UDF found on SSC (renamed by me to distinguish functionality from my other 2 udf_Split% UDFs):
USE [DBA]
GO
/****** Object: UserDefinedFunction [dbo].[udf_Split8Kv] Script Date: 01/09/2012 09:26:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_Split8Kv]
/***************************************************************************************************
Purpose: Split a given string at a given delimiter and return a list of the split elements (items).
Returns: iTVF containing the following:
ItemNumber = Index Position of the Item as an int, begins at ZERO.
Item = Element value as a VARCHAR(8000)
CROSS APPLY Usage Example:
-----------------------------------------------------------------------------------------------------
===== Conditionally drop the test tables to make reruns easier for testing.
-- (this is NOT a part of the solution)
IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL
DROP TABLE #JBMTest;
--===== Create and populate a test table on the fly (this is NOT a part of the solution).
SELECT *
INTO #JBMTest
FROM ( SELECT 1,'1,10,100,1000,10000,100000,1000000'
UNION ALL
SELECT 2,'2000000,200000,20000,2000,200,20,2'
UNION ALL
SELECT 3, 'This,is,a,test'
UNION ALL
SELECT 4, 'and so is this'
UNION ALL
SELECT 5, 'This, too (no pun intended)'
UNION ALL
SELECT 6, LEFT(REPLICATE('Thisisanunbrokenstring',400),7999)
) d (SomeID,SomeValue);
GO
--===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
SELECT test.SomeID
, split.ItemNumber
, split.Item
FROM #JBMTest test
CROSS APPLY ( SELECT ItemNumber
, Item
FROM dbo.udf_Split8Kv(test.SomeValue,',')
) split;
***************************************************************************************************/
--===== Define I/O parameters
( @pString varchar(7999)
, @pDelimiter char(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
--===== "Inline" CTE Driven "Tally Table” produces values up to
-- 10,000... enough to cover VARCHAR(8000)
WITH E1 ( N )
AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2 ( N )
AS ( SELECT 1
FROM E1 a
, E1 b
) -- 100
, E4 ( N )
AS ( SELECT 1
FROM E2 a
, E2 b
) --10,000
, cteTally ( N )
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT N ) )
FROM E4
)
--===== Do the split
SELECT ROW_NUMBER() OVER ( ORDER BY N ) - 1 AS IDX
, SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Value
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
GO