How to split a string in t-sql

If a list of values are concatenated by a character like '1,2,3,4,5', we need to split the string to a temp table so each value can be used in sql query. There are many tricks of doing this but the easiest one I have seen is below:


DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'

-- Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @N INT
-- Add start and end commas to the Parameter so we can handle
-- single elements

SET @Parameter = ','+@Parameter +','

SET @N = 1

-- Loop through and find each comma, then insert the string value
-- found between the current comma and the next comma. @N is
-- the position of the current comma.
WHILE @N < LEN(@Parameter) --Don't include the last comma
BEGIN
INSERT INTO @Elements
VALUES (SUBSTRING(@Parameter, @N+1, CHARINDEX(',', @Parameter, @N+1) - @N-1))
-- Find the next comma
SELECT @N = CHARINDEX(',',@Parameter,@N+1)

END

SELECT * FROM @Elements