分享 SQL SERVER 中运用完整的正则表达式——RegexWorkbench2000 之二 replace
The OLE Regex Replace function-----------------------------*/IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL DROP FUNCTION dbo.RegexReplaceGOCREATE FUNCTION dbo.RegexReplace ( @pattern VARCHAR(255), @replacement VARCHAR(255), @Subject VARCHAR(8000), @global BIT = 1, @Multiline bit =1 )RETURNS VARCHAR(8000)/*The RegexReplace function takes three string parameters. The pattern (the regular expression) the replacement expression, and the subject string to do the manipulation to.The replacement expression is one that can cause difficulties. You can specify an empty string '' as the @replacement text. This will cause the Replace method to return the subject string with all regex matches deleted from it (see "strip all HTML elements out of a string" below). To re-insert the regex match as part of the replacement, include $& in the replacement text. (see "find a #comment and add a TSQL --" below) If the regexp contains capturing parentheses, you can use backreferences in the replacement text. $1 in the replacement text inserts the text matched by the first capturing group, $2 the second, etc. up to $9. (e.g. see import delimited text into a database below) To include a literal dollar sign in the replacements, put two consecutive dollar signs in the string you pass to the Replace method.*/AS BEGIN DECLARE @objRegexExp INT, @objErrorObject INT, @strErrorMessage VARCHAR(255), @Substituted VARCHAR(8000), @hr INT, @Replace BIT SELECT @strErrorMessage = 'creating a regex object' EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT IF @hr = 0 SELECT @strErrorMessage = 'Setting the Regex pattern', @objErrorObject = @objRegexExp IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern IF @hr = 0 /*By default, the regular expression is case sensitive. Set the IgnoreCase property to True to make it case insensitive.*/ SELECT @strErrorMessage = 'Specifying the type of match' IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1 IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global IF @hr = 0 SELECT @strErrorMessage = 'Doing a Replacement' IF @hr = 0 EXEC @hr= sp_OAMethod @objRegexExp, 'Replace', @Substituted OUT, @subject, @Replacement /*If the RegExp.Global property is False (the default), Replace will return the @subject string with the first regex match (if any) substituted with the replacement text. If RegExp.Global is true, the @Subject string will be returned with all matches replaced.*/ IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '') RETURN @strErrorMessage END EXEC sp_OADestroy @objRegexExp RETURN @Substituted ENDGO--remove repeated words in textSELECT dbo.RegexReplace('\b(\w+)(?:\s+\1\b)+', '$1', 'Sometimes I cant help help help stuttering',1, 1)--find a #comment and add a TSQL --SELECT dbo.RegexReplace('#.*','--$&','# this is a commentfirst,second,third,fourth',1,1)--replace a url with an HTML anchorSELECT dbo.RegexReplace('\b(https?|ftp|file)://([-A-Z0-9+&@#/%?=~_|!:,.;]*[-A-Z0-9+&@#/%=~_|])', '<a href="$2">$2</a>', 'There is this amazing site at http://www.simple-talk.com',1,1)--strip all HTML elements out of a stringSELECT dbo.RegexReplace('<(?:[^>''"]*|([''"]).*?\1)*>', '','<a href="http://www.simple-talk.com">Simle Talk is wonderful</a><!--This is a comment --> we all love it',1,1)--import delimited text into a database, converting it into insert statementsSELECT dbo.RegexReplace('([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+', 'Insert into MyTable (Firstcol,SecondCol, ThirdCol, Fourthcol) select $1,$2,$3,$4','1|white gloves|2435|245652|Sports Shoes|285678|09873|Stumps|2845|9874|bat|29862|4875',1,1)/*