I just want to share this function, since there are a lot of version around, which are not resistant against zero positions advance, for instance, if you split ‘1,2,3’ into a table, it would be find, but what if one element is empty, such as ‘1,,3’? This function deals with it setting returning a null element.

Usage:

SELECT * FROM [udf_SplitVarchar2Table]('one,two,three', ',')

returns:

ALTER FUNCTION [dbo].[udf_SplitVarchar2Table]
(
    @List varchar(max),
    @delimiter VARCHAR(10)
)

RETURNS
    @Values TABLE(col VARCHAR(512))
AS

BEGIN 
    IF @List IS NULL OR LEN(@List) = 0 RETURN;
 
  SET @List = replace(@List,CHAR(39)+CHAR(39),CHAR(39))
 
  DECLARE @Index INT=1; 
  DECLARE @ItemValue varchar(100);  
  DECLARE @pos INT = 1;
  DECLARE @l INT = LEN(@List);

  WHILE @Index > 0   
    BEGIN        
      SET @Index = CHARINDEX(@Delimiter,@List, @pos);  
   
      IF @Index  > 0 
            IF (@index- @pos> 0)
                SET @ItemValue = SUBSTRING(@List,@pos, @index- @pos );
            ELSE
                SET @ItemValue=NULL;
      ELSE
        IF (@l-@pos+1)>0
            SET @ItemValue =SUBSTRING( @List, @pos, @l-@pos+1) ;
        ELSE
            SET @ItemValue = NULL;

      INSERT INTO @Values (col) VALUES (@ItemValue);    
      SET @pos = @index+1;
    END
    RETURN;
END