What a useful trick, and definitely something I'll need to remember.

However, I think the function has a bug.  When I put through either of the following...

SELECT

* FROM dbo.fnSetSplit(',C,,,E')

SELECT

* FROM dbo.fnSetSplit(',,C,,,E')

...I get odd results before 'C'.  Instead, I knocked up the below with some adjustments:

i) the Numbers table starts from 0, not 1

ii) accepts a variable delimiter, including one of variable length;

iii) returns a row number so that you can choose the nth value.

iv) use "like" for comparisons as SQL Server can do funny things comparing spaces

It does have a "quirk", however.  If the delimiter is a multiple of the same character you can get odd results.  Whether this is an error or not I think depends on the expected usage.

SELECT

* FROM dbo.fnSetSplitSV(',,C,,,E',',') -- picks up the "missing" A

SELECT

* FROM dbo.fnSetSplitSV(' TA fred TA mary TA albert',' TA ') -- variable delimiter

SELECT

* FROM dbo.fnSetSplitSV(',,,C,,,E',',,') -- multiple delimiter with "quirk"

S.

CREATE FUNCTION [dbo].[fnSetSplitSV] (@String VARCHAR(8000), @Delim VARCHAR(5))

RETURNS

@SplitTable TABLE

(

Row

int identity

, Num int

, String varchar(8000)

)

AS

BEGIN

DECLARE @DelimLen int

SELECT @DelimLen = datalength(@Delim)

INSERT INTO @SplitTable

SELECT

Num

, SUBSTRING(@String

, CASE Num + @DelimLen

WHEN @DelimLen THEN @DelimLen

ELSE Num + @DelimLen

END - @DelimLen

, CASE CHARINDEX(@Delim, @String, Num)

WHEN 0 THEN LEN(@String) - Num + @DelimLen

ELSE CHARINDEX(@Delim, @String, Num) - Num

END

) AS String

FROM dbo.Numbers

WHERE Num <= LEN(@String)

AND (SUBSTRING(@String, Num - @DelimLen, @DelimLen) like @Delim OR Num = 0)

RETURN

END