Find and replace/wrap certain terms in html tags I'm trying to replace certain text in a html string with a html anchor tag surrounding that text, I've tried using REPLACE to simply find and replace the term but then it ends up finding matches inside words and html attributes which I don't want. The closest I've got to it actually working is using PATINDEX and STUFF shown in the example below however it only replaces the first occurrence and it replaces the boundary characters (i.e. the [ ,.] chars) as well as the actual term.

Example

DECLARE @start TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY,
    val VARCHAR(100) NOT NULL
);

DECLARE @end TABLE (
    id INT PRIMARY KEY,
    val VARCHAR(100) NOT NULL
);

INSERT INTO @start 
VALUES ('change me plz'),('change me and me plz too'),('dont change this meme'),('nope'),('&lt;a title="don''t click me ever"&gt;not me&lt;/a&gt; and change me')

DECLARE @searchTerm VARCHAR(50) = 'me';
DECLARE @replaceTerm VARCHAR(50) = '&lt;a href="about:blank"&gt;me&lt;/a&gt;';
DECLARE @pattern VARCHAR(50) = '%[ ,.]' + @searchTerm + '[ ,.]%';
DECLARE @currentVal VARCHAR(100)
DECLARE @currentId INT = (
    SELECT TOP 1 id FROM @start WHERE val like @pattern
);

SELECT * FROM @start

WHILE(@currentId IS NOT NULL)
BEGIN
PRINT @currentId;
    SELECT @currentVal = STUFF(val, PATINDEX(@pattern, val), LEN(@searchTerm)+2, @replaceTerm)
    FROM @start
    WHERE id = @currentId

    INSERT INTO @end (id, val)
    VALUES (@currentId, @currentVal)

    DELETE FROM @start
    WHERE
    id = @currentId

    SET @currentId = (
        SELECT TOP 1 id FROM @start WHERE val like @pattern
    );
END

SELECT * FROM @end


tl;dr;

how do you add html tags around text in a html string so that the html is still valid and you only replace the whole word/term not substrings of other words
SQL Server is not the best with String Manipulation.  If this functionality will change over time or become complex in any nature, I would suggest you look into CLR as a way to search/replace complex strings.  Here's a great article from Phil Factor on Simple Talk that walks through how to use CLR for complex string operations.