SQLServer 移除HTML函数

SQL 1265次浏览 本站
--使用方法:SELECT TOP 10 SUBSTRING(dbo.RemoveHtml(content), 1, 50) + '...' summary FROM RC_Blog_Article

ALTER   FUNCTION [dbo].[RemoveHtml] ( @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
        SET @HTMLText = REPLACE(@htmlText, ' ', '')
        SET @HTMLText = REPLACE(@htmlText, '"', '"')
        SET @HTMLText = REPLACE(@htmlText, '&', '&')
        SET @HTMLText = REPLACE(@htmlText, '€', '€')
        SET @HTMLText = REPLACE(@htmlText, '<', '<')
        SET @HTMLText = REPLACE(@htmlText, '>', '>')
        SET @HTMLText = REPLACE(@htmlText, 'œ', 'oe')
        SET @HTMLText = REPLACE(@htmlText, ' ', ' ')
        SET @HTMLText = REPLACE(@htmlText, '©', '?')
        SET @HTMLText = REPLACE(@htmlText, '«', '?')
        SET @HTMLText = REPLACE(@htmlText, '®', '?')
        SET @HTMLText = REPLACE(@htmlText, '±', '±')
        SET @HTMLText = REPLACE(@htmlText, '²', '2')
        SET @HTMLText = REPLACE(@htmlText, '³', '3')
        SET @HTMLText = REPLACE(@htmlText, 'µ', 'μ')
        SET @HTMLText = REPLACE(@htmlText, '·', '·')
        SET @HTMLText = REPLACE(@htmlText, 'º', 'o')
        SET @HTMLText = REPLACE(@htmlText, '»', '?')
        SET @HTMLText = REPLACE(@htmlText, '¼', '?')
        SET @HTMLText = REPLACE(@htmlText, '½', '?')
        SET @HTMLText = REPLACE(@htmlText, '¾', '?')
        SET @HTMLText = REPLACE(@htmlText, '&Aelig', '?')
        SET @HTMLText = REPLACE(@htmlText, 'Ç', '?')
        SET @HTMLText = REPLACE(@htmlText, 'È', 'è')
        SET @HTMLText = REPLACE(@htmlText, 'É', 'é')
        SET @HTMLText = REPLACE(@htmlText, 'Ê', 'ê')
        SET @HTMLText = REPLACE(@htmlText, 'Ö', '?')
        SET @HTMLText = REPLACE(@htmlText, 'à', 'à')
        SET @HTMLText = REPLACE(@htmlText, 'â', 'a')
        SET @HTMLText = REPLACE(@htmlText, 'ä', '?')
        SET @HTMLText = REPLACE(@htmlText, 'æ', '?')
        SET @HTMLText = REPLACE(@htmlText, 'ç', '?')
        SET @HTMLText = REPLACE(@htmlText, 'è', 'è')
        SET @HTMLText = REPLACE(@htmlText, 'é', 'é')
        SET @HTMLText = REPLACE(@htmlText, 'ê', 'ê')
        SET @HTMLText = REPLACE(@htmlText, 'ë', '?')
        SET @HTMLText = REPLACE(@htmlText, 'î', '?')
        SET @HTMLText = REPLACE(@htmlText, 'ô', '?')
        SET @HTMLText = REPLACE(@htmlText, 'ö', '?')
        SET @HTMLText = REPLACE(@htmlText, '÷', '÷')
        SET @HTMLText = REPLACE(@htmlText, 'ø', '?')
        SET @HTMLText = REPLACE(@htmlText, 'ù', 'ù')
        SET @HTMLText = REPLACE(@htmlText, 'ú', 'ú')
        SET @HTMLText = REPLACE(@htmlText, 'û', '?')
        SET @HTMLText = REPLACE(@htmlText, 'ü', 'ü')
        SET @HTMLText = REPLACE(@htmlText, '"', '"')
        SET @HTMLText = REPLACE(@htmlText, '&', '&')
        SET @HTMLText = REPLACE(@htmlText, '‹', '<')
        SET @HTMLText = REPLACE(@htmlText, '›', '>')
        RETURN LTRIM(RTRIM(@HTMLText))
    END 
            

发表评论

电子邮件地址不会被公开。 必填项已用*标注