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

public partial class RegExp
{
// 验证字符串中是否包含与指定的匹配模式一致的字符串
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegExIsMatch(SqlString expression, SqlString pattern)
    {
        return new SqlBoolean(Regex.IsMatch(expression.ToString(), pattern.ToString()));
    }

// 替换字符串中与指定的匹配模式一致的字符串
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString RegExReplace(SqlString expression, SqlString pattern, SqlString replacement)
    {
        return new SqlString(Regex.Replace(expression.ToString(), pattern.ToString(), replacement.ToString()));
    }

// 提取字符串中与指定的匹配模式一致的字符串
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString RegExSubstring(SqlString expression, SqlString pattern, SqlInt32 position, SqlInt32 occurrence)
    {
        if (expression.ToString().Length < position) return new SqlString("");
        if (position <= 0) position = 1;
        if (occurrence <= 0) occurrence = 1;

        Match m = Regex.Match(expression.ToString().Substring((int) position - 1),pattern.ToString());
        for (int i = 1; i < (int)occurrence; i++)
        {
            m = m.NextMatch();
            if (!m.Success) return new SqlString("");
        }

        return new SqlString(m.ToString());
    }

// 计算字符串中与指定的匹配模式一致的字符串的数目
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlInt32 RegExCount(SqlString expression, SqlString pattern, SqlInt32 position)
    {
        if (expression.ToString().Length < position) return 0;
        if (position <= 0) position = 1;

        MatchCollection ms = Regex.Matches(expression.ToString().Substring((int)position - 1), pattern.ToString());

        return new SqlInt32(ms.Count);
    }

// 查找字符串中与指定的匹配模式一致的字符串的位置
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlInt32 RegExIndex(SqlString expression, SqlString pattern, SqlInt32 position, SqlInt32 occurrence)
    {
        if (expression.ToString().Length < position) return 0;
        if (position <= 0) position = 1;
        if (occurrence <= 0) occurrence = 1;

        Match m = Regex.Match(expression.ToString().Substring((int)position - 1), pattern.ToString());
        for (int i = 1; i < (int)occurrence; i++)
        {
            m = m.NextMatch();
            if (!m.Success) return 0;
        }

        return new SqlInt32(m.Index + 1);
    }
}

// 启用 SQL Server 的 CLR 支持
exec sp_configure 'clr enabled',1;
go
reconfigure
go

// 导入程序集
if exists (select * from sys.assemblies where name='RegExp')
 drop assembly RegExp;
go
create assembly RegExp authorization dbo
from 'fullpath\ClrRegExClass.dll'
with permission_set=safe;
go

// 创建函数
// @expression 为输入的字符串；@pattern 为正则表达式；
// @position 为字符串开始的位置；@occurrence 为与指定的匹配模式一致的字符串出现的次数

if object_id('dbo.regex_ismatch','FS') is not null
 drop function dbo.regex_ismatch;
go
create function dbo.regex_ismatch
(@expression nvarchar(max), @pattern nvarchar(max))
returns bit with returns null on null input
as external name RegExp.RegExp.RegExIsMatch;
go

-- 验证字符串是否以 [server] 开头
-- select dbo.regex_ismatch('[server].[database].[schema].[object]','^\[server\]');

if object_id('dbo.regex_replace','FS') is not null
 drop function dbo.regex_