SqlServer2019通过调用C#dll使用正则

1.场景：

项目中需要提取文字中的Emails，但是sqlserver无法直接使用正则表达式

2.代码：

1）从创建一个【类库】-（建议直接通用【.NET Framework】的类库，不然导致代码内的【Microsoft.SqlServer.Server.SqlFunction】找不到引用）

using System.Text.RegularExpressions;

namespace MSSQLRegexExtend
{
    /// <summary>
    /// SQLServer正则验证
    /// </summary>
    public class RegexExtend
    {
        const string MatchEmailPattern =
          @"(([\w-]+\.)+[\w-]+|([a-zA-Z]{1}|[\w-]{2,}))@"
          + @"((([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\.([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\."
            + @"([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\.([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])){1}|"
          + @"([a-zA-Z]+[\w-]+\.)+[a-zA-Z]{2,4})";

        /// <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);
        }

        /// <summary>
        /// 正则提取
        /// </summary>
        /// <param name="regex"></param>
        /// <param name="input"></param>
        /// <param name="splitStr">分割号</param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static string Extract(string regex,string input,string splitStr=";")
        {
            Regex rx = new Regex(regex, RegexOptions.Compiled | RegexOptions.IgnoreCase);
            // Find matches.
            MatchCollection matches = rx.Matches(input);
            // Report the number of matches found.
            int noOfMatches = matches.Count;
            // Report on each match.
            var result = "";
            foreach (Match match in matches)
            {
                result += match.Value + splitStr;
            }
            return result;
        }

        /// <summary>
        /// 正则提取邮箱
        /// </summary>
        /// <param name="input"></param>
        /// <param name="splitStr">分割号</param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static string ExtractEmails(string input, string splitStr = ";")
        {
            Regex rx = new Regex(MatchEmailPattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
            // Find matches.
            MatchCollection matches = rx.Matches(input);
            // Report the number of matches found.
            int noOfMatches = matches.Count;
            // Report on each match.
            var result = "";
            foreach (Match match in matches)
            {
                result += match.Value + splitStr;
            }
            return result;
        }
    }
}

 

2）SQLServer代码

--添加信任
DECLARE @hash AS BINARY(64) = (SELECT HASHBYTES('SHA2_512', (SELECT * FROM OPENROWSET (BULK 'D:\CSharp\MSSQLRegexExtend\MSSQLRegexExtend.dll', SINGLE_BLOB) AS [Data])))

EXEC sp_add_trusted_assembly @hash
--查看添加情况
select * from master.sys.trusted_assemblies


--DROP ASSEMBLY Regex  
CREATE ASSEMBLY Regex from 'D:\CSharp\MSSQLRegexExtend\MSSQLRegexExtend.dll' WITH PERMISSION_SET = SAFE --注册.net类库  

EXEC 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]  

/****正则提取****/  
--DROP FUNCTION [dbo].[Regex.Extract]  
CREATE FUNCTION [dbo].[Regex.Extract](@Regex [nvarchar](max),@Input [nvarchar](max),@SplitStr [nvarchar](max))  
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER  
AS   
EXTERNAL NAME [Regex].[MSSQLRegexExtend.RegexExtend].[Extract]  

/****正则提取文本中的邮箱****/  
--DROP FUNCTION [dbo].[Regex.ExtractEmails]  
CREATE FUNCTION [dbo].[Regex.ExtractEmails](@Input [nvarchar](max),@SplitStr [nvarchar](max))  
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER  
AS   
EXTERNAL NAME [Regex].[MSSQLRegexExtend.RegexExtend].[ExtractEmails]  

 

3.使用

SELECT ReturnTo,Attn,
[dbo].[Regex.ExtractEmails]([Attn],';') as Emails,
* FROM dbo.Table

 

感谢：https://blog.csdn.net/Codec007/article/details/17373795

https://www.cnblogs.com/mingjing/p/7765568.html

https://www.cnblogs.com/bluecool/p/9299072.html