在SQL Server中使用RegEx

Question

我正在寻找如何使用RegEx替换/编码文本基于下面的RegEx设置/参数：

RegEx.IgnoreCase = True     

RegEx.Global = True     

RegEx.Pattern = "[^a-z\d\s.]+"   

我在RegEx上看到了一些例子，但对于如何在SQL Server中以相同的方式应用它感到困惑。任何的意见都将会有帮助。谢谢。

Answer 1

-- MS SQL using VBScript Regex-- select dbo.RegexReplace('aa bb cc','($1) ($2) ($3)','([^\s]*)\s*([^\s]*)\s*([^\s]*)')-- $$ dollar sign, $1 - $9 back references, $& whole matchCREATE FUNCTION [dbo].[RegexReplace](&nbsp; &nbsp;-- these match exactly the parameters of RegExp&nbsp; &nbsp; @searchstring varchar(4000),&nbsp; &nbsp; @replacestring varchar(4000),&nbsp; &nbsp; @pattern varchar(4000))RETURNS varchar(4000)ASBEGIN&nbsp; &nbsp; declare @objRegexExp int,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; @objErrorObj int,&nbsp; &nbsp; &nbsp; &nbsp; @strErrorMessage varchar(255),&nbsp; &nbsp; &nbsp; &nbsp; @res int,&nbsp; &nbsp; &nbsp; &nbsp; @result varchar(4000)&nbsp; &nbsp; if( @searchstring is null or len(ltrim(rtrim(@searchstring))) = 0) return null&nbsp; &nbsp; set @result=''&nbsp; &nbsp; exec @res=sp_OACreate 'VBScript.RegExp', @objRegexExp out&nbsp; &nbsp; if( @res <> 0) return '..VBScript did not initialize'&nbsp; &nbsp; exec @res=sp_OASetProperty @objRegexExp, 'Pattern', @pattern&nbsp; &nbsp; if( @res <> 0) return '..Pattern property set failed'&nbsp; &nbsp; exec @res=sp_OASetProperty @objRegexExp, 'IgnoreCase', 0&nbsp; &nbsp; if( @res <> 0) return '..IgnoreCase option failed'&nbsp; &nbsp; exec @res=sp_OAMethod @objRegexExp, 'Replace', @result OUT,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@searchstring, @replacestring&nbsp; &nbsp; if( @res <> 0) return '..Bad search string'&nbsp; &nbsp; exec @res=sp_OADestroy @objRegexExp&nbsp; &nbsp; return @resultEND您需要在SQL中启用Ole Automation Procedures：exec sp_configure 'show advanced options',1;&nbsp;goreconfigure;&nbsp;gosp_configure 'Ole Automation Procedures', 1;&nbsp;goreconfigure;&nbsp;gosp_configure 'show advanced options',0;&nbsp;goreconfigure;go