SplitArray is a powerful table SQL function which splits delimited values into a row-set. One major use of this function is to use it to split a comma separated Ids which are passed into a stored procedure. Enjoy!

Usage

SELECT
	*
FROM
	dbo.SplitArray('Jay,Steve,Novella,Bob,Burnside,James,Randi,Rebecca,Watson', ',')

Output

Function

CREATE FUNCTION SplitArray
(
	@String VarChar(Max),
	@Delimiter VarChar(100) = ','
)
RETURNS @Array TABLE  
(
	Value VarChar(Max)
)
AS
BEGIN
	DECLARE @LastIndex Int
	DECLARE @Index Int
	DECLARE @Length Int

	DECLARE @TmpArray TABLE  
	(
		Value VarChar(Max)
	)

	IF @String IS NOT NULL
	BEGIN--IF
		-------------------------------------------------------------------------------------------

		WHILE(IsNull(@Index, 1) > 0)
		BEGIN--WHILE
			-------------------------------------------------------------------------------------------
			SET @Index = CharIndex(@Delimiter, @String, IsNull(@Index, 0) + 1)

			IF @Index > 0
			BEGIN--IF
				SET @Length = @Index - IsNull(@LastIndex, 0) - 1
			END ELSE BEGIN--IF
				SET @Length = LEN(@String) - IsNull(@LastIndex, 0)
			END--IF

			INSERT INTO @TmpArray(Value)
			SELECT LTrim(RTrim(SubString(@String, IsNull(@LastIndex, 0) + 1, @Length)))

			SET @LastIndex = @Index + LEN(@Delimiter) - 1
			-------------------------------------------------------------------------------------------
		END--WHILE

		-------------------------------------------------------------------------------------------
	END--IF

	INSERT INTO @Array(Value) SELECT DISTINCT * FROM @TmpArray
	RETURN
END