Function For Getting Comma Seperated Values In Sql Server

• Tweet
• 

        // function for getting comma seperated values in sql server 

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- This function splits a variable-length parameter array (actually a string
-- with comma as a delimiter) and stored the values into the table.
-- CHARINDEX() function is used to identify the position of the first delimiter
-- in the text and SUBSTRING() function is used to set the 'element' column.

ALTER FUNCTION [dbo].[GetCSVValues](
    @string    varchar(550) -- '1,2,3,5,6,7'
)
RETURNS @table TABLE(element int)
AS
BEGIN
DECLARE @tempvarchar(550),
    @delimPos AS tinyint
     
SET @delimPos = 0         
SET @temp= LTRIM(RTRIM(@string))
        
WHILE CHARINDEX(',',@temp) > 0 
 BEGIN 
 SET @delimPos = CHARINDEX(',',@temp)
 INSERT INTO @table(element) VALUES (CAST((LEFT(@temp,@delimPos-1)) AS smallint))

 SET @temp= RTRIM(LTRIM(SUBSTRING(@temp,@delimPos+1,LEN(@temp)-@delimPos))) 
 END 

INSERT INTO @table(element) VALUES (CAST((@temp) AS smallint))

RETURN
END

Tags:
• CSV
• function
• server
• split
• SQL