SQL去除HTML标签及换行符失败，求解决方法

你的SQL代码无法彻底去除HTML标签，核心原因是SQL Server的REPLACE函数不支持正则表达式。你写的<a[^>]*>这类正则匹配模式，REPLACE会把它当成普通字符串处理，无法匹配任意格式的<a>标签开头，自然没法替换掉完整的标签内容，导致残留。

SQL Server可以借助XML的特性来解析HTML标签，直接提取其中的纯文本内容，步骤如下：

1. 先将文本中转义的HTML字符（如<、>、"、&）还原为实际字符；
2. 把文本转换为XML类型，XML会自动忽略所有标签，只保留文本内容；
3. 处理换行符和多余空格，得到整洁的纯文本。

完整SQL代码 #

DECLARE @text_original NVARCHAR(MAX);
DECLARE @clean_text NVARCHAR(MAX);

SET @text_original = ' several pop gems in their wake.\r\n\nThe groups original linitarist/vocal drummer/vocalist Debbi Peterson responded to an advertisement that guitarist/vocalist <a href=\"spotify:artist:2Sc4ukCRllIu02LZfHF0RL\">Susanna Hoffs</a> had placed in a local Los Angeles paper, The Recycler. Taking the name the Bangs, the trio released a single, \"Getting Out of Hand\"/\"Call on Me,\" on their own label, Downkiddie. They had to change their name early the following year to the Bangles, since there was already a New York-based group called the Bangs. After an appearance on a Rodney on the ROQ compilation and a series of local concerts which featured new bassist Annette Zilinskas, Miles Copeland signed the Bangles to the <a href=\"spotify:search:label%3A%22IRS%22\">IRS</a> subsidiary <a href=\"spotify:search:label%3A%22Faulty+Products%22\">Faulty himself, and when it came time to find a producer for the Bangles fifth record, <a href=\"spotify:artist:2Sc4ukCRllIu02LZfHF0RL\">Hoffs</a> didnt have far to look. The resulting sunny, very California-sounding Sweetheart of the Sun was released in September 2011. The following years saw the band regularly touring while playing some big shows like 2012s Rewind Festival, the 50th anniversary celebration for the famous L.A. nightclub the Whisky a Go Go, and 2013s Paisley Aboveground, which featured the Bangles playing alongside reunited paisley underground bands <a href=\"spotify:artist:3iayA3QkB3jfkUgcXqsL7C\">the Three OClock</a> and <a href=\"spotify:artist:1e9cOgMYTLJ0KFOjmTx13F\">Rain Parade</a>. In 2014, the band released Ladies and Gentlemen... The Bangles!, a compilation of early singles, EP tracks, and rarities. In 2019 the band appeared alongside <a href=\"spotify:artist:1e9cOgMYTLJ0KFOjmTx13F\">Rain Parade</a>, <a href=\"spotify:artist:1l0eT7EY5r7U1gMVm2SyoS\">the Dream Syndicate</a>, and <a href=\"spotify:artist:3iayA3QkB3jfkUgcXqsL7C\">the Three OClock</a> on the Paisley Underground compilation 3X4, which saw each band covering a song by the other three groups. ~ Stephen Thomas Erlewine & Andrew Leahey, Rovi'

-- 步骤1：还原转义的HTML字符
SET @text_original = REPLACE(REPLACE(REPLACE(REPLACE(@text_original, '&lt;', '<'), '&gt;', '>'), '&quot;', '"'), '&amp;', '&');

-- 步骤2：利用XML提取纯文本
SET @clean_text = CAST(@text_original AS XML).value('.', 'NVARCHAR(MAX)');

-- 步骤3：处理换行符和多余空格
SET @clean_text = REPLACE(REPLACE(@clean_text, CHAR(13), ' '), CHAR(10), ' ');
SET @clean_text = LTRIM(RTRIM(REPLACE(REPLACE(@clean_text, '  ', ' '), '  ', ' '))); -- 替换连续空格为单个空格

SELECT @clean_text AS clean_text;

补充说明 #

• 如果你的文本中存在XML无法解析的特殊字符（比如未转义的&），可以先做额外处理，避免转换XML时出错；
• 这种方法不仅能处理<a>标签，对其他HTML标签（如<p>、<div>等）也同样有效，适用性更广。

内容的提问来源于stack exchange，提问作者Antonio Avila