Tuesday, 3 January 2012

How to return comma seperator values as table in sql server

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

    DECLARE @String    VARCHAR(8000)
    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

---

ALTER FUNCTION [dbo].[fn_ConvertDelimitedStringToTable]
(
@delimitedString varchar(max),
@delimiter varchar(8) -- should only be single character in most places, but allow multiple characters just in case
)
RETURNS @result TABLE
(
rowID int IDENTITY(1,1),
value varchar(256)
)

BEGIN
DECLARE @commaPosition int, @stringBeingProcessed varchar(max)
IF LEFT(@delimitedString, LEN(@delimiter)) = @delimiter
BEGIN
 -- the delimited string starts with the delimiter
 -- remove this prefix
 SET @delimitedString = SUBSTRING(@delimitedString, LEN(@delimiter) + 1, LEN(@delimitedString) - LEN(@delimiter))
END

IF RIGHT(@delimitedString, LEN(@delimiter)) = @delimiter
BEGIN
 -- the delimited string ends with the delimiter
 -- remove this suffix
 SET @delimitedString = LEFT(@delimitedString, LEN(@delimitedString) - LEN(@delimiter))
END

IF @delimitedString IS NULL OR LEN(LTRIM(RTRIM(@delimitedString))) = 0
BEGIN
 -- handle special case where a NULL or zero-length string is received
 -- if this happens then return an empty result set
 RETURN
END

SET @stringBeingProcessed = @delimitedString
-- Get the position of the first comma (or other delimiter char)
SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
-- If we don't have a comma then that means that we only have one value
IF @commaPosition = 0
BEGIN
 -- Only one definition Name
 INSERT INTO @result
 VALUES (LTRIM(RTRIM(@stringBeingProcessed)))
END
ELSE
BEGIN
 -- We have at least 2 values in our comma-delimited string, insert the first one
 INSERT INTO @result
 VALUES (LTRIM(RTRIM(SUBSTRING(@stringBeingProcessed, 1, @commaPosition - 1))))
 -- Now chop the first value before the first comma
 SET @stringBeingProcessed = RIGHT(@stringBeingProcessed, LEN(@stringBeingProcessed) - (@commaPosition + LEN(@delimiter) - 1))
 -- Get the next comma position
 SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
 -- Loop until no commas left
 WHILE @commaPosition <> 0
 BEGIN
  -- Insert the next value
  INSERT INTO @result
  VALUES (LTRIM(RTRIM(SUBSTRING(@stringBeingProcessed, 1, @commaPosition - 1))))
  -- Chop the next value before the found comma
  SET @stringBeingProcessed = RIGHT(@stringBeingProcessed, LEN(@stringBeingProcessed) - (@commaPosition + LEN(@delimiter) - 1))
  -- Get the next comma position
  SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
 END
 -- If we don't have any more commas then are on the last value
 INSERT INTO @result
 VALUES(LTRIM(RTRIM(@stringBeingProcessed)))
END

RETURN
END


select * from dbo.ufn_CSVToTable('1,2,4,5,8')
select * from dbo.fn_ConvertDelimitedStringToTable('1,2,4,5,8',',')

No comments:

Post a Comment