SQL Server Split function

In the past I have used a function do these types of splits.  Here is an example of parsing out a comma seperated value.  It's probably not the most efficient, but get things done in a data processing scenario.

--Function Call
SELECT
  dbo.fn_ParseComma(ColumnName,0,1) -- Start at beginning (0) and go until 1st Comma
 ,dbo.fn_ParseComma(ColumnName,1,2) -- Start after 1st Comma until 2nd Comma
 ,dbo.fn_ParseComma(ColumnName,2,99) --After 2nd Comma Until End of Row
FROM tblName

Select allOpen in new window

Function:

CREATE FUNCTION [dbo].[fn_ParseComma] 
( 
    @str nVarChar(Max), 
    @startcomma Int,
    @endcomma Int
) 
RETURNS nVarChar(max) 
AS 
BEGIN 
    
    Declare @i as Int,
            @j as Int,
            @startpos Int,
            @endpos Int,  
            @result nVarChar(max)

    SET @i = 1
    SET @j = 0

    While @i <= Len(@str)
      BEGIN

        --If Start Comma is "first character"
        if @startcomma = 0 and @i = 1
        BEGIN
          SET @startpos = 1
        END

        --If End Comma is "last character"
        if @endcomma = 99 and @i = len(@str)
        BEGIN
          SET @endpos = @i+1 - @startpos
        END

        If SubString(@str,@i,1) = ','  --Replace comma with other character if necessary.
        BEGIN
          SET @j = @j + 1

          --If we Match Ending Comma Position
          if @j = @endcomma
          BEGIN
            SET @endpos = @i - @startpos
            SET @i = Len(@Str)
          END

          --If we Match Starting Comma Position
          If @j = @startcomma
          BEGIN
            SET @startpos = @i+1
          END

        END

      SET @i = @i + 1
    END

   
    --SET @Result = (SELECT '(' + Cast(@startpos as varchar) + ',' + Cast(@EndPos as varchar) + ')' + SubString(@str,@startpos,@endpos))
    SET @Result = (SELECT SubString(@str,@startpos,@endpos))
    RETURN @Result 
END 

Select allOpen in new window

ALTER FUNCTION [dbo].[fn_StringToTable] (@string VARCHAR(MAX), @delimiter CHAR(1))
RETURNS @output TABLE(data varchar(256))
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END
    RETURN
END