Delimited List to Table
Peter Debetta published the article "Delimited List to Table" and stated in the article "I'm sure this could be optimized in a number of ways, and could also be made to trim extra spaces as well. If you have any suggestions, please add them as comments for one and all to see.".
I thought the function was too verbose for a function which just split a string into a table of values, so I optimized his function and I am posting here the result for appreciation. Any comments are welcome.
Regards,
Luciano Evaristo Guerche
Jacarei, SP, Brazil
CREATE FUNCTION dbo.Split2Table(@WhatToSplit varchar(8000), @Delimiter char(1))
RETURNS @ReturnTable TABLE (ListValue Sql_Variant)
AS
BEGIN
DECLARE @StartAt int
DECLARE @DelimiterPosition int
SET @StartAt = 1
SET @DelimiterPosition = COALESCE(NULLIF(CHARINDEX(@Delimiter, @WhatToSplit, @StartAt), 0), LEN(@WhatToSplit) + 1)
WHILE @DelimiterPosition BETWEEN @StartAt AND LEN(@WhatToSplit) + 1
BEGIN
INSERT INTO @ReturnTable
(ListValue)
VALUES
(SUBSTRING(@WhatToSplit, @StartAt, @DelimiterPosition - @StartAt))
SET @StartAt = @DelimiterPosition + 1
SET @DelimiterPosition = COALESCE(NULLIF(CHARINDEX(@Delimiter, @WhatToSplit, @StartAt), 0), LEN(@WhatToSplit) + 1)
END
RETURN
END
GO
/*
-- Some tests below
/*
-- Some tests below
/*
-- Some tests below
SELECT * FROM [dbo].[Split2Table]('', ',')
GO
SELECT * FROM [dbo].[Split2Table](',', ',')
GO
SELECT * FROM [dbo].[Split2Table]('a ,b ,c ,d ,e ', ',')
GO
SELECT * FROM [dbo].[Split2Table](' a, b, c, d, e', ',')
GO
SELECT * FROM [dbo].[Split2Table]('a,,b,,c,,d,,', ',')
GO
*/