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

龍子

--====================================--标题: 应用实例之SQL SERVER 正则替换--作者：maco_wang--时间：2012-03-24--说明：MS-SQL SERVER 中的正则替换--补充说明：支持一下CSDN社区支持的活动--====================================/*假设测试数据为：col----------------------192abc168xx0yya101baaa10hh176bag98job121zerohello112u19aa110beyp45a80abab230pppp120qu224121nile21reply30vall90想要得到的结果（把字段中的连续的字母替换成'.'）：col--------------192.168.0.10110.176.98.121112.19.110.4580.230.120.224121.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 allselect '10hh176bag98job121zero' union allselect 'hello112u19aa110beyp45' union allselect 'a80abab230pppp120qu224' union allselect '121nile21reply30vall90'--本示例在SQL SERVER 2005版本即可适用。--正常思路--a)游标循环截取（略）--b)自定义函数gocreate function [dbo].[fn_replace](@str nvarchar(100))returns varchar(100)asbeginwhile patindex('%[a-z]%',@str) > 0beginset @str = stuff(@str,patindex('%[a-z]%',@str),1,N'.');endwhile (charindex('..',@str)<>0)beginset @str=replace(@str,'..','.')endif(left(@str,1)='.') set @str=right(@str,len(@str)-1)if(right(@str,1)='.') set @str=left(@str,len(@str)-1)return @strendgoselect dbo.[fn_replace](col) as col from [tb]/*col---------------192.168.0.10110.176.98.121112.19.110.4580.230.120.224121.21.30.90*/--c)其他方法，这里我主要介绍一下正则替换，因为patindex和like 能够支持的正则还是非常少的--正则替换--开启xp_cmdshell--不开启会提示：SQL Server blocked access to procedure 'xp_cmdshell'gosp_configure 'show advanced options', 1goreconfiguregosp_configure 'xp_cmdshell', 1goreconfigure--开启sp_OACreate--不开启会提示：SQL Server blocked access to procedure 'sys.sp_OACreate'gosp_configure 'show advanced options', 1;goreconfigure;gosp_configure 'ole automation procedures', 1;goreconfigure;go--创建函数create function [dbo].[regexReplace](@source varchar(8000),    --字符串@regexp varchar(500),     --正则表换式@replace varchar(500),    --替换值@globalReplace bit = 0,   --是否是全局替换@ignoreCase bit = 0       --是否忽略大小写)returns varchar(1000) ASbegindeclare @hr intdeclare @objRegExp intdeclare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp outputif @hr <> 0 beginexec @hr = sp_OADestroy @objRegExpreturn nullendexec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexpif @hr <> 0 beginexec @hr = sp_OADestroy @objRegExpreturn nullendexec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplaceif @hr <> 0 beginexec @hr = sp_OADestroy @objRegExpreturn nullendexec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCaseif @hr <> 0 beginexec @hr = sp_OADestroy @objRegExpreturn nullend exec @hr = sp_OAMethod @objRegExp, 'Replace', @result output, @source, @replaceif @hr <> 0 beginexec @hr = sp_OADestroy @objRegExpreturn nullendexec @hr = sp_OADestroy @objRegExpif @hr <> 0 beginreturn nullend/* 注释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 @resultend--查看结果goselect 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..224121....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.224121.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.10110.176.98.121112.19.110.4580.230.120.224121.21.30.90*/--这个例子对正则的应用还是比较简单的，我这里只是介绍一个思路。