SQL Server 2005：正则表达式使模式匹配和数据提取变得更容易

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 ) );
    }
}

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( ) );
}

create proc ImportCustomers
(
    @file nvarchar(max)
)
as

declare @pattern nvarchar(max)

set @pattern = N'(?<CustomerNumber>\d{7}),
    (?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n'

insert [Customer]
(
    [CustomerNumber],
    [CustomerName],
    [CustomerType]
)
select
    f.[CustomerNumber],
    f.[CustomerName],
    f.[CustomerType]
from dbo.RegExGroups( @file, @pattern ) regex
pivot
(
    max([Text])
    for [Group]
    in ( [CustomerNumber], [CustomerName], [CustomerType] )
) as f