由于sql是一门数据库查询的若语言，在字符串处理上，SQL Server的内置函数功能不如C#有很多选择。如果要实现一些复杂的字符串处理功能，就需要编一些一些自定义的sql函数，本文讲述了如何通过编写一个sql函数来实现正则替换。

现在有一个小场景，数据库中某表的一个字段存储的是html代码，假如现在需要替换掉html代码中的所有<a>标签。

我们当然可以在C#中这样做：

Regex regex = new Regex(@"<a[^>]*>[^<]*</a>"); string cleanedHtml = regex.Replace(html, "");

可是我并不想再写个循环去遍历每条记录，然后保存每条记录，我想在数据库中一步到位，而sql只提供了简单的replace函数，这个函数明显不能达到咱的要求，那就去写一个自定义函数吧。

函数源代码如下：

--SQL正则替换函数

CREATE function dbo.regexReplace

 ( @source ntext, --原字符串 

   @regexp varchar(1000), --正则表达式 

   @replace varchar(1000), --替换值 

   @globalReplace bit = 1, --是否是全局替换 

   @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 

需要注意的是，即使写好了这个函数，也并不能马上使用。执行这个函数时可能会出现以下的错误：

Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

这是因为未开启Ole Automation Procedures选项，MSDN中的Ole Automation Procedures选项。执行下面的语句开启这个选项：

sp_configure 'show advanced options', 1; GO
RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO
RECONFIGURE; GO

所有的准备工作都已经做好，那就试验一下吧。

Example1：忽略大小写并替换

select dbo.regexReplace('<A HREF="www.jileiba.com" target="_blank" style="color:red;">123456</a>','<a[^>]*>[^<]*</a>','',1,1)

Example2: 使用贪婪匹配

html代码：

<p> Also Available - <a style="text-decoration: none" href="/isbn/9780199218691"><font color="#000FF"><b>Smith & Hogan: Criminal Law Cases & Materials 10th ed</b></font></a>
  <p> There is, as ever, detailed analysis of the many recent case developments, in particular,
    a revision of the chapter dealing with secondary liability and joint enterprise.</p>
</p> 

调用代码：

select dbo.regexReplace(html,'<a[^>]*>(.|\n)*?</a>','',1,1)

Example3：去除html标签

select dbo.regexReplace('<p><b>Key Contact:</b><br> Mr Jack, Zhou<br> General Manager<br> <p> Mr Adu, Ho<br> Marketing Director<br> Overseas Sales<br> <p> Ms Winny, Luo<br> Sales Manager<br> Overseas Sales<br> <p>' ,'<[^>]*>','',1,0) 

Example4：数据库字段值替换

update Books set [Description] = dbo.regexReplace([Description],'<a[^>]*>(.|\n)*?</a>','',1,1)