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

叶子 2012-03-24 02:11:04

加精



--====================================

--标题: 应用实例之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,那么修改正则表达式，把连续的替换成'.'试一试

select dbo.regexReplace(col,'[a-z]{1,100}','.',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

*/



--结果还是不相同

--开头结尾还是有多余的'.'

--不想用left,right,substring截取，修改正则能不能搞定呢？



--再修改一下

select 

col=dbo.regexReplace(dbo.regexReplace(col,'[a-z]{1,100}','.',1,0),'^\.{1}|\.{1}$','',1,0)

from [tb]



/*

col

----------------

192.168.0.101

10.176.98.121

112.19.110.45

80.230.120.224

121.21.30.90

*/



--这个例子对正则的应用还是比较简单的，我这里只是介绍一个思路。