分享一段SQL 自定义函数 过滤HTML标签的源码
SQL 自定义函数 过滤HTML标签----过滤HTML字符-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReplaceHTML]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ReplaceHTML]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.ReplaceHTML
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)
exec @hr = sp_OACreate 'VBScript.RegExp ', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern ', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global ', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase ', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace ', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
end
return @result
end
GO
--------------------------------------------------
----开启 配置选项
---------------------------------------------------
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
----调用方法------------------------------------------------
select [dbo].[ReplaceHTML]('<p>ssss</p>' , '<(.[^>]*)>', ' ',1,1) as HTML
