A Table Valued Function to Split Strings


-- the function
CREATE FUNCTION SplitString
(
      @TargetString NVARCHAR(MAX),
      @Delimeter NVARCHAR(MAX)
)
 
-- the part repository
RETURNS @Parts TABLE
(
      PartId INT IDENTITY(1, 1),
      Part VARCHAR(MAX)
)
AS BEGIN
 
      -- just some variables to keep track of things
      DECLARE
            @CurrentIndex INT,
            @DelimeterIndex INT,
            @PartLength INT;
 
      -- initialize the loop
      SELECT
            @CurrentIndex = 0,
            @DelimeterIndex =CHARINDEX(@Delimeter, @TargetString, 0),
            @PartLength = @DelimeterIndex - @CurrentIndex;
 
      -- if the delimeter exists, continue the loop
      WHILE (@DelimeterIndex > 0) BEGIN
 
            -- add the part to the part repository
            INSERT INTO @Parts VALUES (SUBSTRING(@TargetString, @CurrentIndex, @PartLength));
 
            -- update the indexing information
            SELECT
                  @CurrentIndex = @CurrentIndex + @PartLength + LEN(@Delimeter),
                  @DelimeterIndex = CHARINDEX(@Delimeter, @TargetString, @CurrentIndex),
                  @PartLength = @DelimeterIndex - @CurrentIndex;
 
      END
 
      -- add whatever comes after the last delimeter
      INSERT INTO @Parts VALUES (SUBSTRING(@TargetString, @CurrentIndex, LEN(@TargetString) - @CurrentIndex + 1))
 
      RETURN
END