在SQLServer中使用正则表达式
本文来自:
http://www.cnblogs.com/sdlfx/archive/2011/01/23/1942696.html
sqlserver对于字符串的处理比较弱，replace函数也仅仅支持用字符串来作为匹配项，要是需要使用正则表达式，就只能通过系统存储过程来调用VB组件来实现了，下面是两个写好的正则函数：
测试是否匹配：
GO
CREATEFUNCTIONdbo.regexIsMatch
(
@sourcevarchar(5000),--需要匹配的源字符串
@regexpvarchar(1000),--正则表达式
@ignorecasebit=0--是否区分大小写，默认为false
)
RETURNSbit--返回结果-false，-true
AS
BEGIN
--1（成功）,是由OLE自动化对象返回的HRESULT的整数值。
DECLARE@hrinteger
--用于保存返回的对象令牌，以便之后对该对象进行操作
DECLARE@objRegExpintegerDECLARE@objMatchesinteger
--保存结果
DECLARE@resultsbit
/*
创建OLE对象实例,只有sysadmin固定服务器角色的成员才能执行sp_OACreate,并确定机器中有VBScript.RegExp类库
*/
EXEC@hr=sp_OACreate'VBScript.RegExp',@objRegExpOUTPUT
IF@hr<>0BEGIN
SET@results=0
RETURN@results
END
EXEC@hr=sp_OASetProperty@objRegExp,'Pattern',@regexp
IF@hr<>0BEGIN
SET@results=0
RETURN@results
END
EXEC@hr=sp_OASetProperty@objRegExp,'Global',false
IF@hr<>0BEGIN
SET@results=0
RETURN@results
END
EXEC@hr=sp_OASetProperty@objRegExp,'IgnoreCase',@ignorecase
IF@hr<>0BEGIN
SET@results=0
RETURN@results
END
--调用对象方法
EXEC@hr=sp_OAMethod@objRegExp,'Test',@resultsOUTPUT,@source
IF@hr<>0BEGIN
SET@results=0
RETURN@results
END
--释放已创建的OLE对象
EXEC@hr=sp_OADestroy@objRegExp
IF@hr<>0BEGIN
SET@results=0
RETURN@results
END
RETURN@results
END正则替换：
GO
--SQL正则替换函数
CREATEFUNCTIONdbo.regexReplace
(
@sourcentext,--原字符串
@regexpvarchar(1000),--正则表达式
@replacevarchar(1000),--替换值
@globalReplacebit=1,--是否是全局替换
@ignoreCasebit=0--是否忽略大小写
)
RETURNSvarchar(1000)AS
BEGIN
DECLARE@hrinteger
DECLARE@objRegExpinteger
DECLARE@resultvarchar(5000)
EXEC@hr=sp_OACreate'VBScript.RegExp',@objRegExpOUTPUT
IF@hr<>0BEGIN
EXEC@hr=sp_OADestroy@objRegExp
RETURNnull
END
EXEC@hr=sp_OASetProperty@objRegExp,'Pattern',@regexp
IF@hr<>0BEGIN
EXEC@hr=sp_OADestroy@objRegExp
RETURNnull
END
EXEC@hr=sp_OASetProperty@objRegExp,'Global',@globalReplace
IF@hr<>0BEGIN
EXEC@hr=sp_OADestroy@objRegExp
RETURNnull
END
EXEC@hr=sp_OASetProperty@objRegExp,'IgnoreCase',@ignoreCase
IF@hr<>0BEGIN
EXEC@hr=sp_OADestroy@objRegExp
RETURNnull
END
EXEC@hr=sp_OAMethod@objRegExp,'Replace',@resultOUTPUT,@source,@replace
IF@hr<>0BEGIN
EXEC@hr=sp_OADestroy@objRegExp
RETURNnull
END
EXEC@hr=sp_OADestroy@objRegExp
IF@hr<>0BEGIN
RETURNnull
END
RETURN@result
END关键是sp_OACreate,sp_OADestroy,sp_OASetProperty,sp_OAMethod这几个存储过程的使用，在《编写扩展存储过程》一文里也有这个用法。测试替换函数：printdbo.regexReplace('sfasdf234sdfds','\D','',1,0)使用时需要开启对OLE存储过程的使用：execsp_configure'OleAutomationProcedures',1reconfigure在设置此配置时有可能会提示“配置选项'OleAutomationProcedures'不存在，也可能是高级选项。”的错误，解决办法是在配置之前执行语句：execsp_configure'showadvancedoptions',1reconfigure