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)asbegin    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 @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) ASbegin    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 @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,那麼修改Regex，把連續的替換成'.'試一試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*/--這個例子對正則的應用還是比較簡單的，我這裡只是介紹一個思路。