How to replace URL text in <a href>.. string?

Question

I have some data my SQL Server 2012 in a column comments of datatype nvarchar(max).

Sample data:

For more information, please visit www.sample.com 
If you desire submit a request please go here: www.request.com
Customer Service.

I need to get this:

For more information, please visit <a href="www.sample.com">www.sample.com</a> 
If you desire submit a request please go here: <a href="www.request.com">www.request.com</a>
Customer Service."

All links start with www. Thanks for your help

Answer 1

You could create a function like this, taking in the original string and then returning the result w/ html:

CREATE FUNCTION dbo.ufnUrlHref (
    @str nvarchar(max)
)

RETURNS nvarchar(max)
AS
BEGIN

DECLARE @nextMatch int = patindex('%www.%', @str); --find start of next 'www.'
DECLARE @result nvarchar(max) = '';

WHILE (@nextMatch != 0)
BEGIN
    DECLARE @matchEnd int = charindex(' ', @str, @nextMatch);
    DECLARE @strLen int = len(@str);
    DECLARE @first nvarchar(max) = substring(@str, 1, @strLen - (@strLen - @matchEnd)); --get all of string up to first url
    DECLARE @last nvarchar(max) = substring(@str, @matchEnd + 1, @strLen - @matchEnd); --get rest of string after first url
    DECLARE @url nvarchar(255) = substring(@str, @nextMatch, @matchEnd - @nextMatch); --get url
    SET @first = replace(@first, @url, '<a href="' + @url + '">' + @url + '</a>'); --replace url w/ full href
    SET @result = @result + @first; --add updated string to result

    --set up for next run
    SET @str = @last; --remove corrected section from string
    SET @nextMatch = patindex('%www.%', @str); --find start of next 'www.'
END --end while

IF @str IS NOT NULL --add any remaining text back into result
BEGIN
    SET @result = @result + @str;
END

RETURN @result;

END;