一、创建 .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 'F:\工具\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
