SQL Server 2008 clearhtml函数清除字段中的HTML标记

淩亂°似流年 2022-08-23 08:49 45阅读 0赞

create function [dbo].[clearhtml] (@maco varchar(max))
    returns varchar(max) as begin
        declare @i int
        while 1 = 1
        begin
           set @i=len(@maco)
           set @maco=replace(@maco, substring(@maco,charindex('<',@maco),
           charindex('>',@maco)-charindex('<',@maco)+1),space(0))
           if @i=len( @maco )
           break
        end
     
        set @maco=replace(@maco,' ','')
        set @maco=replace(@maco,' ','')
        set @maco=ltrim(rtrim(@maco))
        set @maco=replace(@maco,char(9),'')
        set @maco=replace(@maco,char(10),'')
        set @maco=replace(@maco,char(13),'')
     
        return (@maco)
    end

加强版：

create function [dbo].[clearhtml_V2] (@maco varchar(max))
    returns varchar(max)
    as
    begin
        declare @randchar_one nvarchar(200)
        declare @randchar_two nvarchar(200)
           if(charindex('<<',@maco)>0)
                  begin
                         set @randchar_one='D4678B36-B958-4274-B81E-BBA636CFB427';
                         set @randchar_two='49E374CC-9E1A-4850-897C-27074DE32E7F';
                         set @maco=replace(@maco,'<<',@randchar_one)
                         set @maco=replace(@maco,'>>',@randchar_two)
                  end
        declare @i int
        while 1 = 1
        begin
           set @i=len(@maco)
           set @maco=replace(@maco, substring(@maco,charindex('<',@maco),
           charindex('>',@maco)-charindex('<',@maco)+1),space(0))
           if @i=len( @maco )
           break
        end
     
        set @maco=replace(@maco,' ','')
        set @maco=replace(@maco,' ','')
        set @maco=ltrim(rtrim(@maco))
        set @maco=replace(@maco,char(9),'')
        set @maco=replace(@maco,char(10),'')
        set @maco=replace(@maco,char(13),'')
        if(charindex(@randchar_one,@maco)>0)
        begin
           set @maco=replace(@maco,'D4678B36-B958-4274-B81E-BBA636CFB427','<<')
           set @maco=replace(@maco,'49E374CC-9E1A-4850-897C-27074DE32E7F','>>')
        end
        return (@maco)
    end

转自：[叶子博客][Link 1]

[Link 1]: http://blog.csdn.net/maco_wang/article/details/6254946