I don't know why this isn't part of the built-in functions, especially in sql 2005 but anyway, here it is thanks to this
groups post:
CREATE FUNCTION Split(@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int
SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
Then you can do something like this:
select Items from dbo.Split(@List, ',')