Sqlserver使用正则表达式

作者: michaelxwang | 来源:发表于2019-02-03 23:57 被阅读1次

一、创建 .NET类库

1. 新建.NET项目

image.png
  • 在选择.NET的版本时,注意选择根据系统中.NET Framework的版本进行寻找
  • 名称可以选择填写MSSQLRegexExtend,也可以选择自己命名,但要注意之后的程序中,名称要保持一致。
image.png

2. 清除新建程序的代码

image.png

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

image.png

4.点击生成MSSQLRegexExtend

image.png

5.找到文件所在路径

image.png image.png image.png

二、运行SQL程序

1. 注册.net类库

注意:运行SQL命令前首先要切换到所需数据库,下面以微软样例数据库Northwind为例。

代码如下:

/****注册.net类库****/
use Northwind
--1.注册.net类库
CREATE ASSEMBLY Regex from 'C:\Users\Public\Documents\MSSQLRegexExtend.dll' WITH PERMISSION_SET = SAFE 

--2.将数据库设置为可以使用clr组件
sp_configure 'clr enabled', 1   

--3.设置可用clr组件。别忘记运行这行进行应用
RECONFIGURE 
        
--4.删除该类库(删除时执行)
--DROP ASSEMBLY Regex
注册.net 类库 文件路径为储存MSSQLRegexExtend.dll 文件的路径
image.png
将数据库设置为可以使用clr组件
image.png image.png

2.将类库中的静态方法注册为函数

/****以下代码将类库中的静态方法注册为函数****/
--1.正则匹配
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.Match]

--2.正则替换
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.Replace] 

--3.正则校验
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.IsMatch]
设置正则匹配
image.png
设置正则替换
image.png
设置正则校验
image.png

三、测试结果

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             --正则校验有门牌号码的记录

  • 正则匹配是保留正则表达式匹配到的值
[dbo].[Regex.Match]('正则表达式',[字段名])
第一个值填: 正则表达式
第二个值填: 字段名
例: [dbo].[Regex.Match]('(\d)+',[Address]) as [门牌号码]
  • 正则替换是将正则表达式匹配到的值替换为所需值
[dbo].[Regex.Replace]('正则表达式',[字段名],'替换值') 
第一个值填: 正则表达式
第二个值填: 字段名
第三个值填: 替换值
例: [dbo].[Regex.Replace]('\d',[Address],'*') as [将门牌号码打码]   
  • 正则校验是比较匹配值是否满足正则表达式
[dbo].[Regex.IsMatch]('正则表达式',[字段名])=1
第一个值填: 正则表达式
第二个值填: 字段名
例: [dbo].[Regex.IsMatch]('\d',[Address])=1
image.png

相关文章

网友评论

    本文标题:Sqlserver使用正则表达式

    本文链接:https://www.haomeiwen.com/subject/ixeysqtx.html