首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

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

2013-09-07 
SQL SERVER 正则替换实例分享--【叶子】----标题: 应用实例之SQL SERV

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,那么修改正则表达式,把连续的替换成'.'试一试
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 没用过正则,NET也是最近才学的。支持叶子
[解决办法]
我收藏了,再来看看。
[解决办法]
引用:
我收藏了,再来看看。

用C#写了一个

string input1 = "192abc168xx0yya101baaa\r\n"
+ "10hh176bag98job121zero\r\n"
+ "hello112u19aa110beyp45\r\n"
+ "a80abab230pppp120qu224\r\n"
+ "121nile21reply30vall90\r\n";
            string pattern = "(?is)[A-Za-z]+";
            string replacement = ".";
            Regex rgx = new Regex(pattern);
            string[] result = rgx.Replace(input1, replacement).Split(new char[] { '\r', '\n' },StringSplitOptions.RemoveEmptyEntries);
            List<string> listreplace =new List<string>();
            foreach (string sresult in result)
            {
                listreplace.Add(sresult.Trim('.'));
            }


/*
listreplace
----------------
192.168.0.101
10.176.98.121
112.19.110.45
80.230.120.224
121.21.30.90
*/


[解决办法]
感谢分享.
[解决办法]
我提供几个示范例.供楼主研究.
转一个正则表达式函数!
create function dbo.regexReplace
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
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

return @result
end
GO

--调用
create table t
(col varchar(50))

insert t
select '12ABCDEFG34' union all
select 'A1B2C3' union all
select 'DDKFDK34KD23'
select cast(dbo.regexReplace(col,'[^0-9]','',1,1) as varchar(30)) as col from t

drop table t
drop function dbo.regexReplace

col                            
------------------------------ 
1234
123


3423

(所影响的行数为 3 行)




T-SQL 正则表达式(CLR 实现)

http://topic.csdn.net/u/20100623/10/20189452-5993-4015-a153-c7efb0f70b6c.html?37053

[解决办法]
T-SQL中的正则表达式

原作者:Cory Koski
发表时间:2003/06/24


        本文来自Cory Koski。Cory写道:“我最近遇到一个问题,就是试图在数据库域中搜索一个正则表达式。还没有一个版本的SQLServer内部支持正则表达式,但我发现了一个将正则表达式的所有优点添加到你的T_SQL应用的方法。为了更容易的使用正则表达式,我们可以使用自定义函数(User Defined Function, UDF)来帮助我们并使工作简洁。”

在这个解决方案中,我们需要SQL Server 2000或更高。我们还需要确定机器中有VBScript.RegExp类库,这随大多数Windows 2000 servers中的Windows Scripting包配有。若你正在使用一个更早版本的Windows,你必须为你的操作系统下载最新版的Windows Scripting。
自定义函数
下面是我的自定义函数,可用来在源字符串中搜索一个正则模式表达式。
CREATE FUNCTION dbo.find_regular_expression
 (
  @source varchar(5000),
  @regexp varchar(1000),
  @ignorecase bit = 0
 )
RETURNS bit
AS
 BEGIN
  DECLARE @hr integer
  DECLARE @objRegExp integer
  DECLARE @objMatches integer
  DECLARE @objMatch integer
  DECLARE @count integer
  DECLARE @results bit
  
  EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END 
  EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OADestroy @objRegExp


  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
 RETURN @results
 END

将这个UDF保存到你的数据库中,并确定有授权来运行它。当然,你也得确保运行它的人有运行sp_OAxxxxx类扩展存储过程的权限。
这个函数已确保正常运行,并且即便是和COM对象一起使用,也还是挺快的。

举例
使用正则表达式的一个地方就是测试特殊字符。我们不搜索所有的特殊字符,而是查找正常字符的匹配项,例如字母和空格。我们看看它的运行。
DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @vchSourceString2 as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- 初始化变量
SET @vchSourceString = 'Test one This is a test!!'
SET @vchSourceString2 = 'Test two This is a test'

-- 我们的正则表达式应该类似于
-- [a-zA-Z ]{}
-- 如: [a-zA-Z ]{10}  ...  一个十字符的字符串

-- 获得字符串长度
SET @intLength = LEN(@vchSourceString)

-- 设置完整的正则表达式
SET @vchRegularExpression = '[a-zA-Z ]{' + 
CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字符
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(
@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString
IF @bitHasNoSpecialCharacters = 1 BEGIN
 PRINT 'No special characters.'
END ELSE BEGIN
 PRINT 'Special characters found.'
END

PRINT '---'

-- 获得字符串长度
SET @intLength = LEN(@vchSourceString2)

-- 设置完整的正则表达式
SET @vchRegularExpression = '[a-zA-Z ]{' + 
CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字符
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(
@vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2
IF @bitHasNoSpecialCharacters = 1 BEGIN
 PRINT 'No special characters.'
END ELSE BEGIN
 PRINT 'Special characters found.'
END

GO
The results for this example would be: 
本例的结果应该是:
Test one This is a test!!
Special characters found.
---
Test two This is a test
No special characters.

结论:
正如你所见,这是一个简单技巧,在特定的场合得到了非常有用的结果。你作为一个T_SQL开发人员,可以在正则表达式库VBScript.RegExp中使用和扩展这个技巧。 


[解决办法]
SQL SERVER 正则轮换实例分享-【叶子】不看真不不知、


[解决办法]
SQL SERVER 正则轮换实例分享-【叶子】
[解决办法]
感谢楼主分享
[解决办法]
SQL SERVER 正则轮换实例分享-【叶子】
[解决办法]

谢谢分享SQL SERVER 正则轮换实例分享-【叶子】
[解决办法]
谢谢分享

[解决办法]
这里好多高手呀,你们写的东西都很好呀!
[解决办法]
谢谢,小弟受益匪浅,你就是咱的偶像SQL SERVER 正则轮换实例分享-【叶子】
[解决办法]
感谢分享
[解决办法]
感谢楼主奉献精神
[解决办法]
谢谢好人,收藏啦
[解决办法]
收藏!学习学习!!!!!!!!!!!!
[解决办法]
用CLR比OLE稳定,用OLE不得不定期重启rundll
[解决办法]
这个比较好,技术贴,很好
[解决办法]
谢谢分享!!!!!
[解决办法]
正则 pattern regex 3q
[解决办法]
SQL SERVER 正则轮换实例分享-【叶子】
[解决办法]
感谢分享
[解决办法]
感谢分享 
SQL SERVER 正则轮换实例分享-【叶子】

热点排行