sql server 正则表达式

最近需要在sql server中使用正则表达式来实现查找替换功能。

本来想使用 visual stuido 的sql server clr功能来实现，这样可以在其中加入c#的正则函数。但是最终部署的时候总是提示 deploy failed ，但是不知道错误原因。所以改成用vba 的 RegExp来实现。

把此功能做成了sql中的function，代码如下：

IF OBJECT_ID(N'dbo.VBARegexMatch') IS NOT NULL   

     DROP FUNCTION dbo.VBARegexMatch  
 GO  
 --开始创建正则替换函数  
   CREATE FUNCTION dbo.VBARegexMatch  
 (  
     @string VARCHAR(MAX),   --要检查的字符串  
     @pattern VARCHAR(255),  --正则pattern  
     @IgnoreCase INT = 0 --0区分大小写 1不区分大小写  
 )  
 RETURNS VARCHAR(8000)  
 AS   
 BEGIN  
     DECLARE @objRegex INT, @retstr VARCHAR(8000)  
     --创建对象  
     EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT  
     --设置属性  
     EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern  
     EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase  
     EXEC sp_OASetProperty @objRegex, 'Global', 1  
     --执行  
     EXEC sp_OAMethod @objRegex, 'Test', @retstr OUT, @string
     --释放  
     EXECUTE sp_OADestroy @objRegex  
     RETURN @retstr  
 END  
 GO  


EXEC sp_configure 'show advanced options'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ole Automation Procedures',1
RECONFIGURE WITH OVERRIDE

满足正则条件的返回True，否则返回False

SELECT dbo.VBARegexMatch('John Smith', '[0-9]', 1)    //return False

SELECT dbo.VBARegexMatch('John Smith', '[^0-9]', 1)    //return True

IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL   
     DROP FUNCTION dbo.RegexReplace  
 GO  
 --开始创建正则替换函数  
   CREATE FUNCTION dbo.RegexReplace  
 (  
     @string NVARCHAR(MAX),   --被替换的字符串  
     @pattern NVARCHAR(255),  --替换模板  
     @replacestr NVARCHAR(255),   --替换后的字符串  
     @IgnoreCase INT = 0 --0区分大小写 1不区分大小写  
 )  
 RETURNS NVARCHAR(4000)  
 AS   
 BEGIN  
     DECLARE @objRegex INT, @retstr NVARCHAR(4000)  
     --创建对象  
     EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT  
     --设置属性  
     EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern  
     EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase  
     EXEC sp_OASetProperty @objRegex, 'Global', 1  
     --执行  
     EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr  
     --释放  
     EXECUTE sp_OADestroy @objRegex  
     RETURN @retstr  
 END  
 GO 

RegexReplace 用来实现正则替换。

For example:

SELECT dbo.RegexReplace('Firstname Lastname', '([a-z]+)\s([a-z]+)', '$2,$1',1)		--reversal first name AND last name
--result:Lastname,Firstname

Select dbo.RegexReplace(N'<p><span style="font-size: 14px; line-height: 115%; font-family: 宋体;">（1）您手脚发凉吗??</span><br/></p>',N'\<[^<>]*\>',N'',1)
--Return （1）您手脚发凉吗??