SQL去除html标签的进阶优化方法(可保留正文中大于或者小于号)

        在开发的过程中，或许会遇到富文本框内容在数据库中需要去掉多余的html标签的需求，但是在正文中带有小于号"<"或者大于号">"的时候用平常的方法特别容易判断错误。下面我将会使用两个函数来实现去除html标签，同时保留小于号"<"或者大于号">"的效果

(不适应于英文且带有小于号"<"或者大于号">"的文档)。

函数一：查询目标字符串在源字符串中第n次出现的位置

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[find]
(
@S_str varchar(8000),    
@T_str varchar(8000),    
@n int                            
)
returns int
as
begin
   declare @idx int
   ;with cte(Str,CharIdx,Times) as
   (
      select @S_str,CHARINDEX(@T_str,@S_str,1) as CharIdx,1 as Times
      union all
      select @S_str,CHARINDEX(@T_str,@S_str,CharIdx+1) as CharIdx,Times+1
      from cte where CHARINDEX(@T_str,@S_str,CharIdx+1)>0
   )
   select @idx=isnull(CharIdx,0) from cte where Times=@n
   return @idx
end

函数二：去除html标签的函数本体

        函数内使用循环的方式，依次对字符串"<"到">" 内的字段进行判断，将"<" 到">"内的字段包含中文而且不包含"style"的留下，把"<"后紧接"/"或者紧接英文的字段去除。(函数二将会使用函数一的方法实现)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[clearhtml] (@maco varchar(8000))

returns varchar(8000) as begin

    declare @i int
	DECLARE @q int
	set @q=1
    while 1 = 1

    begin
       set @i=len(@maco)
            set  @maco= replace (@maco, substring(@maco,dbo.func_find(@maco,'<',@q),charindex('>',@maco,dbo.func_find(@maco,'<',@q))-dbo.func_find(@maco,'<',@q)+1)
	   ,case when LEFT(substring(@maco,dbo.func_find(@maco,'<',@q),charindex('>',@maco,dbo.func_find(@maco,'<',@q))-dbo.func_find(@maco,'<',@q)+1),2) not like '%[a-z]%'
	   and  LEFT(substring(@maco,dbo.func_find(@maco,'<',@q),charindex('>',@maco,dbo.func_find(@maco,'<',@q))-dbo.func_find(@maco,'<',@q)+1),2) not like '%/%'
	   and LEFT(substring(@maco,dbo.func_find(@maco,'<',@q),charindex('>',@maco,dbo.func_find(@maco,'<',@q))-dbo.func_find(@maco,'<',@q)+1),2) not like '%!%'
	    then substring(@maco,dbo.func_find(@maco,'<',@q) ,charindex('>',@maco,dbo.func_find(@maco,'<',@q))-dbo.func_find(@maco,'<',@q)+1)  else  space(0) end) 

       if @i=len( @maco )
	   set @q=@q+1

	   if @i !=len( @maco ) 
	    set @q=1   

	   if  @maco like '%<%' or @maco like '%>%'
		begin
		if substring( REVERSE(@maco),charindex('>', REVERSE(@maco)),charindex('<', REVERSE(@maco),charindex('>', REVERSE(@maco)))) NOT like '%[a-z]%' 
		and substring( REVERSE(@maco),charindex('>', REVERSE(@maco)),charindex('<', REVERSE(@maco),charindex('>', REVERSE(@maco)))) not like '%style%' 
			  break
		end	

		if  @maco NOT like '%<%' or @maco NOT like '%>%'
		  break
    end
    set @maco=replace(@maco,'&nbsp;','')
	set @maco=replace(@maco,'&nbsp','')
    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
GO

看完不妨点个赞支持一下把，秋梨膏！！！