本文描述的 RegexReplace 正则替换存储过程来自于网络，然后经过作者的验证后记录于此。

1、用法

update a set a.simple_name=dbo.RegexReplace(name, '([^\u4e00-\u9fa5]+)', '',1, 0) FROM name_temp a;

2、源码

ALTER FUNCTION dbo.RegexReplace
    (
      @Subject VARCHAR(8000),
      @pattern VARCHAR(255),
      @replacement VARCHAR(255),
      @global BIT = 1,
      @Multiline bit =1
    )
RETURNS VARCHAR(8000)
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.*/   
    
    EXEC sp_OADestroy @objRegexExp
    RETURN @Substituted
   END