sql - 如果与 SQL 中给定的匹配 URL 匹配，如何从正文中删除锚标记

Question

我有一个sample input body下面，其中包含anchor tag-

<p>I personally use a <a href="http://www.prevention.com/fitness/6-must-have-mobility-tools">massage stick</a> on a regular basis, and highly recommend one called the Tiger Tail, which retails for less than $30. I keep it by my bedside to address the exercise-related leg cramps I occasionally experience during the night. Since the cramps I generally get attack my smaller muscles, making them difficult to treat with regular stretching, I use the Tiger Tail instead. After a few minutes of rolling over the cramping muscle, the pain typically disappears or at least has been significantly reduced.</p>

现在，如果下面给出的URL包含在上面body，那么whole anchor tag应该是removed (excluding wrapped text)-

http://www.prevention.com/fitness/6-must-have-mobility-tools

身体的预期输出-

<p>I personally use a massage stick on a regular basis, and highly recommend one called the Tiger Tail, which retails for less than $30. I keep it by my bedside to address the exercise-related leg cramps I occasionally experience during the night. Since the cramps I generally get attack my smaller muscles, making them difficult to treat with regular stretching, I use the Tiger Tail instead. After a few minutes of rolling over the cramping muscle, the pain typically disappears or at least has been significantly reduced.</p>

注意 -解决方案应该generic and dynamic是no static data.

提前致谢！

标签： sqlsql-serverurlanchorhref

Answer 1

I created this SQL function for the purpose described:

create or alter function [dbo].[TMFN_RmAnchorLinksNotText](@HTMLText nvarchar(max))
returns nvarchar(max)
as
begin

    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT

    SET @Start = CHARINDEX('<a',@HTMLText)
    SET @End =CHARINDEX('>',@HTMLText,CHARINDEX('<a',@HTMLText))
    SET @Length = (@End - @Start) + 1

    -- Remove all anchor start-tags, inc. href='xxx'.
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<a',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<a',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END

    -- Remove all anchor end-tags:
    SET @HTMLText = REPLACE(LTRIM(RTRIM(@HTMLText)),'</a>','')


    return @HTMLText
end 

Ref. SQL Script to remove anchor tags in sql server ;)