An Idea can change your life.....

Friday, June 27, 2008

Convert a comma separed values in to table

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

DECLARE @String VARCHAR(10)

WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))

INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END

RETURN
END
GO

No comments: