当前位置: 代码迷 >> Sql Server >> 分享 SQL SERVER 中运用完整的正则表达式——RegexWorkbench2000 之二 replace,该如何解决
  详细解决方案

分享 SQL SERVER 中运用完整的正则表达式——RegexWorkbench2000 之二 replace,该如何解决

热度:530   发布时间:2016-04-27 13:34:50.0
分享 SQL SERVER 中运用完整的正则表达式——RegexWorkbench2000 之二 replace
SQL code
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)/*
  相关解决方案