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'),('<a title="don''t click me ever">not me</a> and change me')
DECLARE @searchTerm VARCHAR(50) = 'me';
DECLARE @replaceTerm VARCHAR(50) = '<a href="about:blank">me</a>';
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
Best Answer
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.