UDF For Splitting Comma Seperated Values

CREATE FUNCTION [dbo].[fnSplit](   

    @sInputList VARCHAR(8000) -- List of delimited items   

  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items   

) RETURNS @List TABLE (item VARCHAR(8000))   

   

BEGIN   

DECLARE @sItem VARCHAR(8000)   

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0   

 BEGIN   

 SELECT   

  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),   

  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))   

    

 IF LEN(@sItem) > 0   

  INSERT INTO @List SELECT @sItem   

 END   

   

IF LEN(@sInputList) > 0   

 INSERT INTO @List SELECT @sInputList -- Put the last item in   

RETURN   

END 

 Now Execute the Function

 SELECT * FROM dbo.[fnSplit]('1,2,3',',')

For Output see the Screenshot

 Another Function for the Same Is:

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1)) 

RETURNS @Results TABLE (Items nvarchar(4000)) 

AS 

BEGIN 

DECLARE @INDEX INT 

DECLARE @SLICE nvarchar(4000) 

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z 

--     ERO FIRST TIME IN LOOP 

SELECT @INDEX = 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(Items) VALUES(@SLICE) 

-- 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 

END 

 Now Execute the Function

  

SELECT * FROM dbo.[Split]('1-2-3','-')