Blog ala Vidar

Split with CTE

CREATE FUNCTION SplitWithCTE
(
   @List VARCHAR(8000),
   @Delimiter CHAR(1),
   @ShowEmpty BIT
)
RETURNS @ret TABLE 
(
   pos INT PRIMARY KEY,
   VALUE VARCHAR(8000)
)
AS
BEGIN
	DECLARE @LEN AS INT
	SET @LEN = LEN(@List) + 1

	;
	WITH a AS
	( 
		SELECT 
		  1 AS nStart, 
		  ISNULL(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @LEN) AS nEnd,
		  RTRIM(LTRIM(SUBSTRING(@List, 1, ISNULL(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @LEN) - 1))) AS VALUE
		UNION All
		SELECT 
		  nEnd+1, 
		  ISNULL(NULLIF(CHARINDEX(@Delimiter, @List, nEnd + 1), 0), @LEN),
		  RTRIM(LTRIM(SUBSTRING(@List, nEnd + 1, ISNULL(NULLIF(CHARINDEX(@Delimiter, @List, nEnd + 1) ,0), @LEN) - nEnd - 1)))
			FROM a
			WHERE nEnd < @LEN
	)

	INSERT INTO @ret
	SELECT 
	  ROW_NUMBER() OVER (ORDER BY nStart),
	  NULLIF(VALUE,'')
		FROM a
		WHERE @ShowEmpty = 1 
		OR LEN(VALUE) > 0

    RETURN
END