A Table Valued Function to Split Strings ~ D. Patrick Caldwell on Software Engineering

Monday, August 11, 2008

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
I really appreciate comments so please feel free to comment on my posts. Whether you agree or disagree, I'd love to hear from you. Also, feel free to link back to your own blog in your comments. You can even subscribe to an RSS feed of the comments on this thread.

© 2008 — , D. Patrick Caldwell, President, Autopilot Consulting, LLC

No comments:

Post a Comment