A SQL Split function

This is so useful it's beyond me why it isn't adopted as standard functionality. Maybe it is in SQL Server 2008. In the meantime, here's how I do it.


CREATE FUNCTION fnSplit(@Data nvarchar(4000),
@Delimiter varchar(10) = ',')
RETURNS @tblSplit TABLE

(ItemId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Item nvarchar(4000) NULL)

AS

BEGIN

DECLARE @Delimiter2 varchar(12),
@item nvarchar(4000),
@iPos int,
@DelimWidth int

--had to do this cuz if they send in a > 9 char delimiter I could not pre and post append the % wildcards
SET @Delimiter2 = @Delimiter
SET @Delimiter2 = ISNULL(@Delimiter2, ',')
SET @DelimWidth = LEN(@Delimiter2)

IF RIGHT(RTRIM(@Data), 1) <> @Delimiter2
SELECT @Data = RTRIM(@Data) + @Delimiter2

IF LEFT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = '%' + @Delimiter2

IF RIGHT(@Delimiter2, 1) <> '%'
SET @Delimiter2 = @Delimiter2 + '%'

SELECT @iPos = PATINDEX(@Delimiter2, @Data)

WHILE @iPos > 0
BEGIN
SELECT @item = LTRIM(RTRIM(LEFT(@Data, @iPos - 1)))
IF @@ERROR <> 0 BREAK
SELECT @Data = RIGHT(@Data, LEN(@Data) - (LEN(@item) + @DelimWidth))
IF @@ERROR <> 0 BREAK
INSERT INTO @tblSplit VALUES(@item)
IF @@ERROR <> 0 BREAK
SELECT @iPos = PATINDEX(@Delimiter2, @Data)
IF @@ERROR <> 0 BREAK
END

RETURN
END



You call it like this:


select item from [reference]..fnSplit( @variable, ',' )


Where @variable is a varchar containing your comma-delineated string. Useful for sticking directly into IN clauses.