I have a problem where I need to replace every base part of URLs in a body of text. However, I only want to replace it in specific URLs. Only URLs that go to "resource.aspx" that have the "source" query string parameter need to get the replacement. Other URLs could have the target base part and we don't want to touch those. The resource urls are expected to appear inside quotes.
I made a UDF that takes the text body, the base URL you want to replace, and the base URL to replace with and searches through the text and does the base URL replacement on any "resource URLs" it finds.
Does the algorithm look sound? Anything I missed? Better ways to do this?
I was sad to learn that REPLACE() does not work with a pattern, even though it's second paramter is named "string_pattern" :(. I was hoping it would work like .NET's Regex.Replace() (I know we don't have regex in t-sql, but a pattern based replace function would be nice sigh)
-- ============================================= -- Author: John Doe -- Create date: 11/29/2016 -- Description: Replaces base URLs in text body of every URL that points to -- resource.aspx that has "source" query string parameter. -- Replaces specified base url with specified replacement -- ============================================= CREATE FUNCTION dbo.udfReplaceResourceBaseURLs ( @TextBody NVARCHAR(MAX), @BaseUrlToReplace NVARCHAR(MAX), @BaseUrlReplacement NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN -- Create Copy of the original body. We will search and modify the copy in the loop DECLARE @EmailBodyCopy NVARCHAR(MAX) SET @EmailBodyCopy = @TextBody DECLARE @ResourceURLPattern NVARCHAR(MAX) = '%' + @BaseUrlToReplace + '/myResource.aspx%source=%' -- Do replacements while resource URLs are found in the copy DECLARE @resourceURLStart INT = PATINDEX(@ResourceURLPattern, @EmailBodyCopy) WHILE @resourceURLStart > 0 BEGIN -- Get the full URL of the found resource URL DECLARE @resourceURLEnd INT = CHARINDEX('"', @EmailBodyCopy, @resourceURLStart) DECLARE @resourceURL NVARCHAR(MAX) = SUBSTRING(@EmailBodyCopy, @resourceURLStart, (@resourceURLEnd - @resourceURLStart)) -- Replace base URL to get the new URL with the desired base URL replaced in DECLARE @newResourceURL NVARCHAR(MAX) = REPLACE(@resourceURL, @BaseUrlToReplace, @BaseUrlReplacement) -- Replace the URL in the original body with our new one SET @TextBody = REPLACE(@TextBody, @resourceURL, @newResourceURL) -- Chop off the front of the body copy up to the end of the current resource URL -- so that we find the next resource url on next pass SET @EmailBodyCopy = SUBSTRING(@EmailBodyCopy, @resourceURLEnd, 999999999) -- get to index infinity to make sure grab to end SET @resourceURLStart = PATINDEX(@ResourceURLPattern, @EmailBodyCopy) END RETURN @TextBody END