Sql正则替换

三⽉ 25 ⽇, 2012 年 5:09 下午 - By

现在有⼀个⼩场景，数据库中某表的⼀个字段存储的是html代码，假如现在需要替换掉html代码中的所有标签。我们当然可以在C#中这样做：

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

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

--函数

CREATE function dbo.regexReplace(

@source ntext, --原字符串

@regexp varchar(1000), --正则表达式@replace varchar(1000), --替换值

@globalReplace bit = 1, --是否是全局替换@ignoreCase bit = 0 --是否忽略⼤⼩写)

returnS varchar(1000) ASbegin

declare @hr integer

declare @objRegExp integerdeclare @result varchar(5000)

exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUTIF @hr <> 0 begin

exec @hr = sp_OADestroy @objRegExpreturn nullend

exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexpIF @hr <> 0 begin

exec @hr = sp_OADestroy @objRegExpreturn nullend

exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplaceIF @hr <> 0 begin

exec @hr = sp_OADestroy @objRegExpreturn nullend

exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCaseIF @hr <> 0 begin

exec @hr = sp_OADestroy @objRegExpreturn nullend

exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replaceIF @hr <> 0 begin

exec @hr = sp_OADestroy @objRegExpreturn nullend

exec @hr = sp_OADestroy @objRegExpIF @hr <> 0 beginreturn nullend

return @resultend

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

Msg 15281, Level 16, State 1, Line 1

blocked access to procedure 'sys.sp_OACreate' of component '' 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_co

这是因为未开启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('123456',']*>[^<]*','',1,1)

Example2: 使⽤贪婪匹配

html代码：

Also Available - color=\"#000FF\">Smith & Hogan: Criminal Law Cases & Materials 10th ed

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.

调⽤代码：

select dbo.regexReplace(html,']*>(.|\\n)*?','',1,1)

Example3：去除html标签

select dbo.regexReplace('

Key Contact:
Mr Jack, Zhou
General Manager

Mr Adu, Ho
Marketing Director
Overseas Sales

Ms Winny, Luo
Sales Manager
Overseas Sales

','<[^>]*>','',1,0)

Example4：数据库字段值替换

update Books

set [Description] = dbo.regexReplace([Description],']*>(.|\\n)*?','',1,1)