Monday, May 01, 2006

Find and Replace

I cruised the net last week looking for a simle "Find and Replace" script. sorta like a split for VB only that I need the replace to occur at every nth character. Not finding what I wanted I ended up writing my own, the idea was that I'd start at the lenght that my string needs to be then crawl backwards until I find a space (or breakpoint) then replace it with my Replace string. There may be a better way.. if you have one feel free to comment.


CREATE FUNCTION dbo.SplitAndReplace
(@String AS VARCHAR(8000), @SplitString CHAR(1), @ReplaceString VARCHAR(255), @BreakInterval AS INT
)
RETURNS VARCHAR(8000)

AS

BEGIN
/*
SET @String = 'This is as very simple text that needs to be broken down for processing in the external system, the process should auto insert an exclamation-pound sign in lew of a space that is at 60 characters'
SET @BreakInterval = 60
SET @ReplaceString = '!#'
SET @SplitString = ' '
*/
DECLARE @X AS INT
DECLARE @Y AS INT
SELECT @X = 0, @Y = 0


WHILE (LEN(RTRIM(LTRIM(@String))) - @Y) / @BreakInterval > 0
BEGIN

WHILE @SplitString <> SUBSTRING(@String, @Y + (@BreakInterval - @X), 1)
BEGIN
SET @X = @X + 1
END


SET @String = STUFF(@String, @Y + (@BreakInterval - (@X )) , 1, @ReplaceString)
SET @Y = @Y + (@BreakInterval - @X)
END
RETURN @String
END

No comments:

Post a Comment