Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Simple function to recursively replace a pattern in a string.
CREATE FUNCTION dbo.RepetitiveReplace_fn
(
@P_String VARCHAR(MAX),
@P_Pattern VARCHAR(MAX),
@P_ReplaceString VARCHAR(MAX),
@P_ReplaceLength INT = 1
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Index INT;
-- Get starting point of pattern
set @Index = patindex(@P_Pattern, @P_String);
while @Index > 0
begin
--replace matching charactger at index
set @P_String = stuff(@P_String, patindex(@P_Pattern, @P_String), @P_ReplaceLength, @P_ReplaceString);
set @Index = patindex(@P_Pattern, @P_String);
end;
return @P_String;
END;
@jkdba

This comment has been minimized.

Copy link
Owner Author

@jkdba jkdba commented Mar 7, 2019

recursive function does not make since here if there is more than 32 indices to replace due to sql recursive/nested trigger limit.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment