Split the comma separated value

Some times we have some values with the delimiter and we need to split that value with the that delimiter and need that value in the column with the help of below code we can achieve this :



CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(MAX) -- List of delimited items
  , @sDelimiter VARCHAR(50) = ',' -- delimiter that separates items
) RETURNS @List TABLE (ID INT,item VARCHAR(8000))


BEGIN
DECLARE @sItem VARCHAR(MAX);
DECLARE @ID INT;
SET @ID = 0;


WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SET @ID = @ID + 1;
 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 @ID, @sItem
 END


IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @ID+1, @sInputList -- Put the last item in
RETURN
END


we can use this function like this :
select item from fnSplit('1,2,3,4,5,6',',');
and result is :
1
2
3
4
5
6