SQL SERVER 正则替换范例分享-【叶子】

--====================================
--标题: 应用实例之SQL SERVER 正则替换
--作者：maco_wang
--时间：2012-03-24
--说明：MS-SQL SERVER 中的正则替换
--补充说明：支持一下CSDN社区支持的活动
--====================================

/*
假设测试数据为：
col
----------------------
192abc168xx0yya101baaa
10hh176bag98job121zero
hello112u19aa110beyp45
a80abab230pppp120qu224
121nile21reply30vall90

想要得到的结果（把字段中的连续的字母替换成'.'）：
col
--------------
192.168.0.101
10.176.98.121
112.19.110.45
80.230.120.224
121.21.30.90
*/

--测试数据:
if object_id('[tb]') is not null drop table [tb]
create table [tb] (col varchar(100))
insert into [tb]
select '192abc168xx0yya101baaa' union all
select '10hh176bag98job121zero' union all
select 'hello112u19aa110beyp45' union all
select 'a80abab230pppp120qu224' union all
select '121nile21reply30vall90'

--本示例在SQL SERVER 2005版本即可适用。

--正常思路
--a)游标循环截取（略）
--b)自定义函数
go
create function [dbo].[fn_replace]
(
	@str nvarchar(100)
)
returns varchar(100)
as
begin
	while patindex('%[a-z]%',@str) > 0
	begin
		set @str = stuff(@str,patindex('%[a-z]%',@str),1,N'.');
	end
    while (charindex('..',@str)<>0)
	begin
		set @str=replace(@str,'..','.')
	end
    if(left(@str,1)='.') set @str=right(@str,len(@str)-1)
    if(right(@str,1)='.') set @str=left(@str,len(@str)-1)
	return @str
end
go
select dbo.[fn_replace](col) as col from [tb]
/*
col
---------------
192.168.0.101
10.176.98.121
112.19.110.45
80.230.120.224
121.21.30.90
*/
--c)其他方法，这里我主要介绍一下正则替换，因为patindex和like 能够支持的正则还是非常少的

--正则替换
--开启xp_cmdshell
--不开启会提示：SQL Server blocked access to procedure 'xp_cmdshell'
go
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'xp_cmdshell', 1
go
reconfigure
--开启sp_OACreate
--不开启会提示：SQL Server blocked access to procedure 'sys.sp_OACreate'
go
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'ole automation procedures', 1;
go
reconfigure;
go

--创建函数
create function [dbo].[regexReplace]
(
	@source varchar(8000),    --字符串
	@regexp varchar(500),     --正则表换式
	@replace varchar(500),    --替换值
	@globalReplace bit = 0,   --是否是全局替换
	@ignoreCase bit = 0       --是否忽略大小写
)
returns varchar(1000) AS
begin
	declare @hr int
	declare @objRegExp int
	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
/* 注释A
--	while (charindex('..',@result)<>0)
--	begin
--		set @result=replace(@result,'..','.')
--	end
--    if(left(@result,1)='.')
--		set @result=right(@result,len(@result)-1)
--    if(right(@result,1)='.')
--		set @result=left(@result,len(@result)-1)
*/
    return @result
end

--查看结果
go
select dbo.regexReplace(col,'[a-z]','.',1,0) as col from [tb]
/*
col
-----------------------
192...168..0...101....
10..176...98...121....
.....112.19..110....45
.80....230....120..224
121....21.....30....90
*/

--根据正则把字母替换成'.'后的结果和想要结果并不相同
--需要把函数中的"注释A"取消注释，才能保证结果相同，那就不如直接用上述自定义函数
--那么有没有其他办法呢？

--tb表中的字符长度为100,那么修改正则表达式，把连续的替换成'.'试一试