-- 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
© 2008 — , D. Patrick Caldwell, President, Autopilot Consulting, LLC
click for more information
No comments:
Post a Comment