如何从 SQL Server 中的字符串中去除 HTML 标签?

2021-12-01 00:00:00 string sql sql-server-2005 html sql-server

我在 SQL Server 2005 中得到了包含 HTML 标记的数据，我想去掉所有这些，只留下标记之间的文本.理想情况下，还可以将 &lt; 之类的内容替换为 < 等.

I've got data in SQL Server 2005 that contains HTML tags and I'd like to strip all that out, leaving just the text between the tags. Ideally also replacing things like &lt; with <, etc.

是否有一种简单的方法可以做到这一点，或者有人已经获得了一些示例 T-SQL 代码?

Is there an easy way to do this or has someone already got some sample T-SQL code?

我无法添加扩展存储过程等，因此更喜欢纯 T-SQL 方法(最好是向后兼容 SQL 2000).

I don't have the ability to add extended stored procs and the like, so would prefer a pure T-SQL approach (preferably one backwards compatible with SQL 2000).

我只想检索带有剥离 HTML 的数据，而不是更新它，因此理想情况下将其编写为用户定义的函数，以便于重用.

I just want to retrieve the data with stripped out HTML, not update it, so ideally it would be written as a user-defined function, to make for easy reuse.

例如转换这个:

<B>Some useful text</B>&nbsp;
<A onclick="return openInfo(this)"
   href="http://there.com/3ce984e88d0531bac5349"
   target=globalhelp>
   <IMG title="Source Description" height=15 alt="Source Description" 
        src="/ri/new_info.gif" width=15 align=top border=0>
</A>&gt;&nbsp;<b>more text</b></TD></TR>

为此:

Some useful text > more text

推荐答案

有一个 UDF 可以完成这里描述的工作:

There is a UDF that will do that described here:

用户定义的去除 HTML 的函数

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
END
GO

请注意，这是针对 SQL Server 2005，但如果您将关键字 MAX 更改为 4000 之类的内容，它也将适用于 SQL Server 2000.

note this is for SQL Server 2005, but if you change the keyword MAX to something like 4000, it will work in SQL Server 2000 as well.

相关文章