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

原创 2012年03月24日 15:09:52
--====================================
--标题: 应用实例之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
*/

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


sql server 2008 如何使用正则更新替换字段内容

将该表的某个字段使用正则替换字符串并建立一个视图,使用该视图修改表的对应的字段。...
  • qq_29729735
  • qq_29729735
  • 2017年08月02日 11:42
  • 2657

SQL Server中利用正则表达式替换字符串

建立正则替换函数,利用了OLE对象,以下是函数代码: --如果存在则删除原有函数 IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL DROP FU...
  • Return_false
  • Return_false
  • 2013年09月24日 17:20
  • 11083

sql server 2008 使用正则替换的问题

上篇提到使用正则表达式替换某个字段的内容,但是后来发现有一些文章被替换成null。经检查,原来这些文章字段内容的太大的导致不能replace。...
  • qq_29729735
  • qq_29729735
  • 2017年08月02日 15:53
  • 2587

SQL 去掉字符串中的字母,数字和符号

declare @i int, @temp nvarchar(1), @return nvarchar(1000),@str varchar(1000); set @i = 1; set @retur...
  • a1017680279
  • a1017680279
  • 2014年04月15日 17:10
  • 4074

SQL Server中利用正则表达式替换字符串

建立正则替换函数,利用了OLE对象,以下是函数代码: [sql] view plain copy --如果存在则删除原有函数  IF OBJECT_ID(N'dbo.Re...
  • qq_27689675
  • qq_27689675
  • 2016年12月03日 12:54
  • 608

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

--==================================== --标题: 应用实例之SQL SERVER 正则替换 --作者:maco_wang --时间:2012-03-24 --说...
  • maco_wang
  • maco_wang
  • 2012年03月24日 15:09
  • 4650

正则表达式regexp_replace

SQL> select ename,regexp_replace(ename,'[[:upper:]]+') from emp; ENAME REGEXP_REPLAC...
  • huangliang0703
  • huangliang0703
  • 2014年10月10日 16:32
  • 495

在SQL中用正则表达式替换html标签

由于数据库的一个表字段中多包含html标签,现在需要修改数据库的字段把html标签都替换掉。当然我可以通过写一个程序去修改,那毕竟有点麻烦。直接在查询分析器中执行,但是MS SQL Server并没有...
  • dhttaso
  • dhttaso
  • 2010年11月30日 14:08
  • 3042

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

[sql] view plaincopyprint? --====================================   --标题: 应用实例之SQL S...
  • xun95h3z
  • xun95h3z
  • 2014年11月13日 10:08
  • 205

SQL SERVER 2008R2 CLR 正则表达式替换

--DECLARE @vSP_Definition NVARCHAR(max)= --'sdfs/***************************************************...
  • linxtong
  • linxtong
  • 2014年04月16日 21:14
  • 1402
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL SERVER 正则替换实例分享--【叶子】
举报原因:
原因补充:

(最多只允许输入30个字)