如何从 SQL Server 中的字符串中去除 HTML 标记? [英] How to strip HTML tags from a string in SQL Server?

问题描述

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

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 < 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.

这篇关于如何从 SQL Server 中的字符串中去除 HTML 标记?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！