SQL CLR函数（以正则表达式为例）

2017-03-04

创建CLR程序集

VS中新建项目，类型为Database，模板选择SQL Server项目，然后在解决方案资源管理器中，选中项目，右键添加，选择“用户定义的函数“，然后在类文件中写入相关代码，最后生成即可。以下是以创建正则表达式相关函数的代码为例：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;

public partial class SqlRegularExpressions
{
    // 是否匹配
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean RegExpIsMatch(string input,string pattern)
    {
        return Regex.IsMatch(input, pattern);
    }

    // 第一个匹配的字符串
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RegExpMatch(string input, string pattern)
    {
        return Regex.Match(input, pattern).Value;
    }

    // 所有匹配的字符串
   [SqlFunction(FillRowMethodName = "FillMatch"
               ,TableDefinition = "[index] int, length int, value nvarchar(4000)")
   ]
    public static IEnumerable RegExpMatches(string input, string pattern)
    {
        return Regex.Matches(input, pattern);
    }

    // 与上面配套的填充函数
    public static void FillMatch(object obj, out int index,out int length, out SqlChars value)
    {
        Match match = (Match)obj;
        index = match.Index;
        length = match.Length;
        value = new SqlChars(match.Value);
    }

    // 正则替换
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RegExpReplace(string input, string pattern, string replacement)
    {
        return Regex.Replace(input, pattern, replacement);
    }
}

部署CLR函数

可以直接用VS部署（生成－＞部署），也可以用以下代码手动创建：
第一步，创建程序集

CREATE ASSEMBLY SqlRegularExpressions 
from 'E:\Study\DotNet\SqlServer_RegExp\bin\Debug\sqlRegExpClass.dll' 
WITH PERMISSION_SET = SAFE
Go

第二步，创建引用程序集的函数

-- Regular Expression Like
CREATE FUNCTION dbo.RegExp_Like
(
	@Input NVARCHAR(MAX),
	@Pattern NVARCHAR(500)
)
RETURNS BIT
AS EXTERNAL NAME 
-- [ASSEMBLY Name].[Class Name].[Function Name]
RegularExpression.SqlRegularExpressions.RegExpIsMatch;
Go

-- First Match Substring
CREATE FUNCTION dbo.RegExp_FirstMatch
(
	@Input NVARCHAR(MAX),
	@Pattern NVARCHAR(500)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME 
RegularExpression.SqlRegularExpressions.RegExpMatch;
Go

-- All Matched Substring
CREATE FUNCTION dbo.RegExp_Matches
(
	@Input NVARCHAR(MAX),
	@Pattern NVARCHAR(500)
)
RETURNS Table([Index] INT,[Length] INT,[Value] NVARCHAR(500))
AS EXTERNAL NAME 
RegularExpression.SqlRegularExpressions.RegExpMatches;
Go

-- Regular Expression Repalce
CREATE FUNCTION dbo.RegExp_Replace
(
	@Input NVARCHAR(MAX),
	@Pattern NVARCHAR(500),
	@Replacement NVARCHAR(500)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME 
RegularExpression.SqlRegularExpressions.RegExpReplace;
GO

使用举例

-- 是否含有汉字
SELECT dbo.RegExp_Like(N'我是帅哥','[\u4e00-\u9fa5]');

-- 第一个含有字母z的单词
SELECT dbo.RegExp_FirstMatch('ablaze beagle choral dozen elementary fanatic','\b\w*z+\w*\b');

-- 所有含有字母z的单词
SELECT * FROM dbo.RegExp_Matches('ablaze beagle choral dozen elementary fanatic','\b\w*z+\w*\b');

运行结果：