Skip to content

Instantly share code, notes, and snippets.

@jkdba

jkdba/MSSQL_RepetitiveReplace_fn.sql

Last active December 1, 2022 20:10
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jkdba/ca13fe8f2a9855c4bdbfd0a5d3dfcda2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jkdba/ca13fe8f2a9855c4bdbfd0a5d3dfcda2 to your computer and use it in GitHub Desktop.
Download ZIP
Simple function to recursively replace a pattern in a string.
Raw
MSSQL_RepetitiveReplace_fn.sql
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
Copy link
Copy Markdown
Author

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