Question

如何在SQL Server中使用正则表达式

Answer 1

Create Function [dbo].[IsMatch](@Reg Varchar(1000),@Source Varchar(4000))
returns sql_variant
As
Begin
Declare @Err Int
Declare @obj Int
Declare @Rst sql_variant

EXEC @Err=Sp_OACreate 'VBScript.RegExp',@obj OUTPUT
If @Err<>0 GoTo LB

EXEC @Err=Sp_OASetProperty @obj,'Pattern',@Reg
If @Err<>0 GoTo LB

EXEC @Err=Sp_OASetProperty @obj,'Global','True'
If @Err<>0 GoTo LB

EXEC @Err=Sp_OASetProperty @obj,'IgnoreCase','False'
If @Err<>0 GoTo LB

EXEC @Err=Sp_OAMethod @obj,'Test',@Rst OUTPUT,@source
If @Err<>0 GoTo LB

EXEC @Err=Sp_OADestroy @obj
If @Err<>0 GoTo LB

Return @Rst
LB:
EXEC Sp_OADestroy @obj
RETURN Null
End

--字符串匹配的
Select dbo.IsMatch('\d','12345')

Create Function [dbo].[MyReplace](@Reg Varchar(1000),@Source Varchar(4000),@Str Varchar(1000))
returns sql_variant
As
Begin
Declare @Err Int
Declare @obj Int
Declare @Rst sql_variant

EXEC @Err=Sp_OACreate 'VBScript.RegExp',@obj OUTPUT
If @Err<>0 GoTo LB

EXEC @Err=Sp_OASetProperty @obj,'Pattern',@Reg
If @Err<>0 GoTo LB

EXEC @Err=Sp_OASetProperty @obj,'Global','True'
If @Err<>0 GoTo LB

EXEC @Err=Sp_OASetProperty @obj,'IgnoreCase','False'
If @Err<>0 GoTo LB

EXEC @Err=Sp_OAMethod @obj,'Replace',@Rst OUTPUT,@source,@Str
If @Err<>0 GoTo LB

EXEC @Err=Sp_OADestroy @obj
If @Err<>0 GoTo LB

Return @Rst
LB:
EXEC Sp_OADestroy @obj
RETURN Null
End
--字符串替换的
select dbo.MyReplace('\d','123A454','')

--字符串提取的,这种方法不会搞
--这两个函数需要开启Ole Automation Procedures配置
/*
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ole Automation Procedures',1
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
*/
--CLR的匹配,查找,截取,统计,分割和提取代码我也有,不知道你要不要

Answer 2

直接使用。先学习正则表达式。