SQL Server中使用正则表达式

一、新建.net类库项目

1. 创建类库项目，名为MSSQLRegexExtend
2. 创建一个类，名为RegexExtend
3. 复制下面代码到类中

using System.Text.RegularExpressions;  
  
namespace MSSQLRegexExtend  
{  
  
    public class RegexExtend  
    {  
        /// <summary>  
        /// 正则匹配  
        /// </summary>  
        /// <param name="regex">正则表达式</param>  
        /// <param name="input">文本</param>  
        /// <returns></returns>  
        [Microsoft.SqlServer.Server.SqlFunction]  
        public static string Match(string regex, string input)  
        {  
            return string.IsNullOrEmpty(input) ? "" : new Regex(regex, RegexOptions.IgnoreCase).Match(input).Value;  
        }  
  
        /// <summary>  
        /// 正则替换  
        /// </summary>  
        /// <param name="regex">正则表达式</param>  
        /// <param name="input">文本</param>  
        /// <param name="replace">要替换的目标</param>  
        /// <returns></returns>  
        [Microsoft.SqlServer.Server.SqlFunction]  
        public static string Replace(string regex, string input, string replace)  
        {  
            return string.IsNullOrEmpty(input) ? "" : new Regex(regex, RegexOptions.IgnoreCase).Replace(input, replace);  
        }  
  
        /// <summary>  
        /// 正则校验  
        /// </summary>  
        /// <param name="regex">正则表达式</param>  
        /// <param name="input">文本</param>  
        /// <returns></returns>  
        [Microsoft.SqlServer.Server.SqlFunction]  
        public static bool IsMatch(string regex, string input)  
        {  
            return !string.IsNullOrEmpty(input) && new Regex(regex, RegexOptions.IgnoreCase).IsMatch(input);  
        }  
    }  
}  

右击项目生成

二、将类库注册到MSSQL中

在数据库中执行如下脚本（类库存放地址得自己修改正确）。

--DROP ASSEMBLY Regex  
CREATE ASSEMBLY Regex from 'E:\CSharp\MSSQLRegexExtend\MSSQLRegexExtend\bin\Release\MSSQLRegexExtend.dll' WITH PERMISSION_SET = SAFE --注册.net类库  
  
sp_configure 'clr enabled', 1   --将数据库设置为可以使用clr组件  
RECONFIGURE         --设置可用clr组件。别忘记运行这行进行应用  
  
/****以下代码将类库中的静态方法注册为函数****/  
  
/****正则匹配****/  
--DROP FUNCTION [dbo].[Regex.Match]  
CREATE FUNCTION [dbo].[Regex.Match](@Regex [nvarchar](max),@Input [nvarchar](max))  
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER  
AS   
EXTERNAL NAME [Regex].[MSSQLRegexExtend.RegexExtend].[Match]  
  
/****正则替换****/  
--DROP FUNCTION [dbo].[Regex.Replace]  
CREATE FUNCTION [dbo].[Regex.Replace](@Regex [nvarchar](max),@Input [nvarchar](max),@Replace [nvarchar](max))  
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER  
AS   
EXTERNAL NAME [Regex].[MSSQLRegexExtend.RegexExtend].[Replace]  
  
/****正则校验****/  
--DROP FUNCTION [dbo].[Regex.IsMatch]  
CREATE FUNCTION [dbo].[Regex.IsMatch](@Regex [nvarchar](max),@Input [nvarchar](max))  
RETURNS [bit] WITH EXECUTE AS CALLER  
AS   
EXTERNAL NAME [Regex].[MSSQLRegexExtend.RegexExtend].[IsMatch]  

三、调用示例

SELECT [CustomerID]  
      ,[CompanyName]  
      ,[ContactName]  
      ,[ContactTitle]  
      ,[City]  
      ,[Region]  
      ,[PostalCode]  
      ,[Country]  
      ,[Phone]  
      ,[Fax]  
      ,[Address]  
      ,[dbo].[Regex.Match]('(\d)+',[Address]) as [门牌号码]     --正则匹配  
      ,[dbo].[Regex.Replace]('\d',[Address],'*') as [将门牌号码打码]   --正则替换  
  FROM [Northwind].[dbo].[Customers]  
  where [dbo].[Regex.IsMatch]('\d',[Address])=1             --正则校验有门牌号码的记录