Another one for me to remember.
Converts a string of comma separated values into a table.
ALTER FUNCTION [dbo].[fnCSVToTable]
(
@csvString varchar(4096)
)
RETURNS @temp TABLE(ID varchar(10),OriginalPosition int)
AS
BEGIN
DECLARE @tmpString VARCHAR(10);
DECLARE @pos int
SET @pos = 0;
WHILE LEN(@csvString) > 0
BEGIN
SET @tmpString =
LEFT(@csvString,
ISNULL(NULLIF(CHARINDEX(',', @csvString)-1,-1),
LEN(@csvString)));
SET @csvString =
SUBSTRING(@csvString,
ISNULL(NULLIF(CHARINDEX(',', @csvString),0),
LEN(@csvString))+1,
LEN(@csvString));
INSERT INTO @temp VALUES (@tmpString,@pos);
SET @pos = @pos+1;
END
SET @tmpString = null;
RETURN
END