Converting a delimited string into a table

This isn't anything new, but we will be building upon this in the next post. At times you want to take a delimited string and have it returned in a table format so you can loop over it or do stuff with it; many people have done this before and I'm sure that I'm drawing on stuff I've seen before. This function works well, you call it like this:

SELECT  *
FROM    dbo.StringToTable('a,b,c,d',',')

Where the first parameter is the string to be converted and the second is the delimiter

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--/***********************************************************************
--***
--*** Function:    [StringToTable]
--*** Purpose:      Takes a delimited string and returns a table
--***   
--***   
--*** Author:    Eric Zierdt
--*** Date Created:   2010-04-15
--*** 
--*** Revision History
--*** Date   Author   Description
--*** 2010-04-15 ez    Created
--*** select * from dbo.StringToTable('a,b,c,d',',')
--***********************************************************************/

CREATE FUNCTION [dbo].[StringToTable]
 (
  @String VARCHAR(MAX),
  @Delimiter CHAR(1) = ','
 )
RETURNS @ReturnList TABLE
 (
  ID INT,
  TableValue VARCHAR(MAX)
 )
AS BEGIN
 DECLARE @ArrayList TABLE
 (
  ID INT, TableValue VARCHAR(MAX)
 )
 DECLARE @Value VARCHAR(MAX)
 DECLARE @NextDelimiterLocation INT

  ---------------------------- 
 -- REMOVE LEADING DELIMITER --
  ---------------------------- 
 SET @String = LTRIM(RTRIM(@String)) + 
 CASE WHEN RIGHT(@String, 1) = @Delimiter THEN ''
 ELSE @Delimiter
 END
 
  ------------------------------------------- 
 -- SET THE LOCATION OF THE FIRST DELIMITER --
  ------------------------------------------- 
 SET @NextDelimiterLocation = ISNULL(CHARINDEX(@Delimiter, @String, 1), 0)  

  ---------------------------------------- 
 -- IF NO DELIMITER THEN SET THE WHOLE   --
 -- STRING TO THE FIRST ROW OF THE TABLE --
  ---------------------------------------- 
 IF @NextDelimiterLocation = 0
 BEGIN
  INSERT INTO @ArrayList
  ( 
   ID
   ,TableValue 
  )
  SELECT  1, @String
 END
 ELSE
 BEGIN
  DECLARE @Counter INT
  SET @Counter = 0
  WHILE @NextDelimiterLocation > 0
  BEGIN
    -----------------------------------------------------
   -- SET THE VALUE PARAM TO DATA BEFORE THE DELIMITER  --
   -- THEN LOOP TO DO THE SAME FOR THE REST OF THE LIST --
    -----------------------------------------------------
   SET @Value = LTRIM(RTRIM(LEFT(@String, @NextDelimiterLocation - 1)))
   IF LEN(@String) > 0 AND @NextDelimiterLocation <= LEN(@String)
   BEGIN
    SET @Counter = @Counter + 1
    INSERT  INTO @ArrayList ( ID, TableValue )
    SELECT  @Counter, @Value
   END
   SET @String = SUBSTRING(@String, @NextDelimiterLocation + LEN(@Delimiter), LEN(@String))
   SET @NextDelimiterLocation = CHARINDEX(@Delimiter, @String, 1)
  END
 END
 INSERT  @ReturnList 
 ( 
  ID, TableValue 
 )
 SELECT  ID, TableValue
 FROM    @ArrayList
 
    RETURN
END