SQL Server 使用正则表达式搜索及替换
在使用正则前,需要启用系统高级选项的OLE自动化功能,启用过程如下:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ole Automation Procedures',1
reconfigure
安全起见,使用完后记得关闭该高级功能:
exec sp_configure 'Ole Automation Procedures',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
搜索 Function:
ALTER FUNCTION [dbo].[RegexMatch]
(
@matchstring VARCHAR(4000),
@pattern VARCHAR(200)
)
RETURNS BIT
AS BEGIN
DECLARE @handle int, @result bit
exec sp_oacreate 'vbscript.regexp', @handle output
exec sp_oasetproperty @handle, 'pattern',@pattern
exec sp_oasetproperty @handle, 'global', 'true'
exec sp_oasetproperty @handle, 'ignorecase', 0
exec sp_oamethod @handle, 'test', @result output,@matchstring
exec sp_oadestroy @handle
return @result
END
使用:
select dbo.RegexMatch(N'abc12345',N'\d{5}')
替换Function
CREATE FUNCTION [dbo].[RegReplace]
(
@matchstring VARCHAR(4000),
@pattern VARCHAR(200),
@replace VARCHAR(200)
)
RETURNS VARCHAR(4000)
AS BEGIN
DECLARE @handle int, @result VARCHAR(4000)
exec sp_oacreate 'vbscript.regexp', @handle output
exec sp_oasetproperty @handle, 'pattern',@pattern
exec sp_oasetproperty @handle, 'global', 1 --全局替换
exec sp_oasetproperty @handle, 'ignorecase', 1 --忽略大小写
exec sp_oamethod @handle, 'Replace', @result output,@matchstring,@replace
exec sp_oadestroy @handle
return @result
END
使用:
select dbo.RegReplace('hello 123','\d{2}','world')