T-SQL 正则表达式(CLR 实现)

Question

创建程序集 ClrRegExClass.cs，
并使用 C:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library ClrRegExClass.cs 编译为 ClrRegExClass.DLL 文件。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);
    }
}
将程序集 ClrRegExClass.DLL 导入 SQL Server，并创建相应的函数。// 启用 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_replace;
go
create function dbo.regex_replace
(@expression nvarchar(max), @pattern nvarchar(max), @replacement nvarchar(max))
returns nvarchar(max) with returns null on null input
as external name RegExp.RegExp.RegExReplace;
go-- 将字符串中 [...] 替换为 "..."
-- select dbo.regex_replace('[server].[database].[schema].[object]','\[([\w]*)\]','"$1"');if object_id('dbo.regex_substring','FS') is not null
 drop function dbo.regex_substring;
go
create function dbo.regex_substring
(@expression nvarchar(max), @pattern nvarchar(max), @position int, @occurrence int)
returns nvarchar(max) with returns null on null input
as external name RegExp.RegExp.RegExSubstring;
go-- 提取字符串中与 [...] 模式匹配的第二次出现的字符串
-- select dbo.regex_substring('[server].[database].[schema].[object]','\[\w*\]',1,2);if object_id('dbo.regex_count','FS') is not null
 drop function dbo.regex_count;
go
create function dbo.regex_count
(@expression nvarchar(max), @pattern nvarchar(max), @position int)
returns int with returns null on null input
as external name RegExp.RegExp.RegExCount;
go-- 计算字符串中与 [...] 模式匹配的字符串的数目
-- select dbo.regex_count('[server].[database].[schema].[object]','\[\w*\]',1);if object_id('dbo.regex_index','FS') is not null
 drop function dbo.regex_index;
go
create function dbo.regex_index
(@expression nvarchar(max), @pattern nvarchar(max), @position int, @occurrence int)
returns int with returns null on null input
as external name RegExp.RegExp.RegExIndex;
go-- 查询字符串中与 [...] 模式匹配的第二次出现的字符串开始的位置
-- select dbo.regex_index('[server].[database].[schema].[object]','\[\w*\]',1,2);
（希望大家指正）

Answer 1

那个sql 语句有疑问，写出来。
太多了。

Answer 2

原来.NET和SQL可以这样用...不错,赞一个...!

Answer 3

以前我在网上看到一个更好的，比这个实用多了，楼主你这个RegExCount和RegExIndex一般不会用到，RegExSubstring更是不可能去使用，无意义的函数。看来看去只有一个RegExIsMatch会需要用到。好了，别说我不厚道，贴一下更强大的CLR函数（不是我写的），其中RegExGroups函数是精华，非常实用：using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;
using System.Diagnostics.CodeAnalysis;public static partial class UserDefinedFunctions 
{
    public static readonly RegexOptions Options =
        RegexOptions.IgnorePatternWhitespace |
        RegexOptions.Singleline;    [SqlFunction]
    public static SqlBoolean RegexMatch(
        SqlChars input, SqlString pattern)
    {
        Regex regex = new Regex( pattern.Value, Options );
        return regex.IsMatch( new string( input.Value ) );
    }    [SqlFunction]
    public static SqlChars RegexGroup(
        SqlChars input, SqlString pattern, SqlString name)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match match = regex.Match(new string(input.Value));
        return match.Success ?
            new SqlChars(match.Groups[name.Value].Value) : SqlChars.Null;
    }    internal class MatchNode
    {
        private int _index;
        public int Index { get { return _index; } }        private string _value;
        public string Value { get { return _value; } }        public MatchNode(int index, string value)
        {
            _index = index;
            _value = value;
        }
    }    internal class MatchIterator : IEnumerable
    {
        private Regex _regex;
        private string _input;        public MatchIterator(string input, string pattern)
        {
            _regex = new Regex(pattern, UserDefinedFunctions.Options);
            _input = input;
        }        public IEnumerator GetEnumerator()
        {
            int index = 0;
            Match current = null;
            do
            {
                current = (current == null) ?
                    _regex.Match(_input) : current.NextMatch();
                if (current.Success)
                {
                    yield return new MatchNode(++index, current.Value);
                }
            }
            while (current.Success);
        }
    }    [SqlFunction(FillRowMethodName = "FillMatchRow",
    TableDefinition = "[Index] int,[Text] nvarchar(max)")]
    public static IEnumerable RegexMatches(SqlChars input, SqlString pattern)
    {
        return new MatchIterator(new string(input.Value), pattern.Value);
    }    [SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters")]
    public static void FillMatchRow(object data,
        out SqlInt32 index, out SqlChars text)
    {
        MatchNode node = (MatchNode)data;
        index = new SqlInt32(node.Index);
        text = new SqlChars(node.Value.ToCharArray());
    }    internal class GroupNode
    {
        private int _index;
        public int Index { get { return _index; } }        private string _name;
        public string Name { get { return _name; } }        private string _value;
        public string Value { get { return _value; } }        public GroupNode(int index, string group, string value)
        {
            _index = index;
            _name = group;
            _value = value;
        }
    }    internal class GroupIterator : IEnumerable
    {
        private Regex _regex;
        private string _input;        public GroupIterator(string input, string pattern)
        {
            _regex = new Regex(pattern, UserDefinedFunctions.Options);
            _input = input;
        }        public IEnumerator GetEnumerator()
        {
            int index = 0;
            Match current = null;
            string[] names = _regex.GetGroupNames();
            do
            {
                index++;
                current = (current == null) ?
                    _regex.Match(_input) : current.NextMatch();
                if (current.Success)
                {
                    foreach (string name in names)
                    {
                        Group group = current.Groups[name];
                        if (group.Success)
                        {
                            yield return new GroupNode(
                                index, name, group.Value);
                        }
                    }
                }
            }
            while (current.Success);
        }
    }    [SqlFunction(FillRowMethodName = "FillGroupRow", TableDefinition =
    "[Index] int,[Group] nvarchar(max),[Text] nvarchar(max)")]
    public static IEnumerable
        RegexGroups(SqlChars input, SqlString pattern)
    {
        return new GroupIterator(new string(input.Value), pattern.Value);
    }    [SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters")]
    public static void FillGroupRow(object data,
        out SqlInt32 index, out SqlChars group, out SqlChars text)
    {
        GroupNode node = (GroupNode)data;
        index = new SqlInt32(node.Index);
        group = new SqlChars(node.Name.ToCharArray());
        text = new SqlChars(node.Value.ToCharArray());
    }
}

Answer 4

函数的注册类似楼主的介绍，我就不重复了（举一反三不会？那你还是别用CLR的正则了，因为表达式的编写更难）。给一段RegExGroups的使用示例，看看有多实用：declare @pattern nvarchar(max),@file nvarchar(max)set @file=N'2309478,Janet Leverling,J
2039748,Nancy Davolio,N
0798124,Andrew Fuller,M
4027392,Robert King,L'set @pattern = N'(?<CustomerNumber>\d{7}),
    (?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n'select
    f.[CustomerNumber],
    f.[CustomerName],
    f.[CustomerType]
from dbo.RegExGroups( @file, @pattern ) regex
pivot
(
    max([Text])
    for [Group]
    in ( [CustomerNumber], [CustomerName], [CustomerType] )
) as f

Answer 5

ggggggggggggggggggggggggggggggggggggggggggggggg