SqlServer正则 替换数据
2021/11/19 19:15:05
本文主要是介绍SqlServer正则 替换数据,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
引用
https://www.cnblogs.com/keepfool/archive/2012/03/25/2416911.html
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: 使用贪婪匹配
declare @html nvarchar(4000)='<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)
这篇关于SqlServer正则 替换数据的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-08Docker下的SqlServer发布订阅启用
- 2023-06-05Docker安装MS SQL Server并使用Navicat远程连接
- 2023-05-25深入浅出 SQL Server CDC 数据同步
- 2023-05-12通过空间占用和执行计划了解SQL Server的行存储索引
- 2023-04-24以SQLserver为例的Dapper详细讲解
- 2022-11-30SQL server高级函数查询
- 2022-11-26SQL SERVER数据库服务器CPU不能全部利用原因分析
- 2022-11-21SQL Server 时间算差值/常用函数
- 2022-11-20调试Archery连接SQL Server提示驱动错误
- 2022-10-22SQL Server 完整、差异备份+完整、差异还原(详细讲解,规避错误)