一、新建.net类库项目
- 创建类库项目,名为
MSSQLRegexExtend
- 创建一个类,名为
RegexExtend
- 复制下面代码到类中
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 --正则校验有门牌号码的记录