MSDN Magazin > Home > Ausgaben > 2007 > February >  SQL Server 2005: Mustervergleich und ...
SQL Server 2005
Mustervergleich und Extrahieren von Daten wird durch Verwendung regulärer Ausdrücke noch einfacher
David Banister

Themen in diesem Artikel:
  • Effiziente SQL-Abfragen mithilfe von regulären Ausdrücken
  • Support von regulären Ausdrücken in SQL Server 2005
  • Verwendung von .NET Regex-Klassen aus SQL Server
  • Effektive Verwendung regulärer Ausdrücke in einer Datenbank
In diesem Artikel werden folgende Technologien verwendet:
SQL Server 2005, .NET Framework
Laden Sie den Code für diesen Artikel herunter: Regex2007_02,exe (154 KB)
Code online durchsuchen
Obwohl T-SQL in den meisten Fällen die Datenverarbeitung äußerst leistungsfähig gestaltet, wird für die Textanalyse oder -Bearbeitung wenig Support bereitgestellt. Der Versuch, eine anspruchsvolle Textanalyse durchzuführen, die integrierte Zeichenfolgenfunktionen verwendet, führt zu äußerst umfangreichen Funktionen und gespeicherten Prozeduren, die schwer zu debuggen und zu verwalten sind. Stehen geeignetere Methoden zur Verfügung?
Reguläre Ausdrücke stellen eine viel effizientere und elegantere Lösung bereit. In erster Linie können sie zum Vergleichen von Text verwendet werden, um Datensätze zu identifizieren. Darüber hinaus bieten sie viele weitere Funktionen. Im Folgenden erfahren Sie, wie Sie eine Vielfalt an Aufgaben – einfache sowie komplexere – durchführen können, deren Durchführung in SQL Server™ 2000 als unpraktisch oder unmöglich galt. In SQL Server 2005 können diese jedoch aufgrund des Supports für das Hosten von Common Language Runtime (CLR) durchgeführt werden.
Reguläre Ausdrücke sind in SQL nicht neu. Mit Oracle 10g wurden integrierte reguläre Ausdrücke eingeführt. Darüber hinaus verwenden viele Open Source-Datenbanklösungen eine Art Bibliothek mit regulären Ausdrücken. Reguläre Ausdrücke standen tatsächlich bereits in früheren Versionen von SQL Server zur Verfügung. Der Prozess war jedoch nicht effizient.
Mithilfe der gespeicherten Prozedur sp_OACreate konnte jedes OLE-Automatisierungsobjekt, das reguläre Ausdrücke implementiert hat, verwendet werden. Dafür musste jedoch zuerst ein COM-Objekt erstellt werden, um anschließend mindestens einen IDispatch-Aufruf durchzuführen und das Objekt zu zerstören. Für die meisten Zwecke war diese Vorgehensweise zu ineffizient und verursachte zudem zu viele Leistungsprobleme. Die einzige Alternative bestand darin, eine erweiterte gespeicherte Prozedur zu erstellen. Jetzt gibt es jedoch SQLCLR, eine CLR-benutzerdefinierte Funktion (User-defined Function, UDF). Mit dieser können Sie einen effizienten und weniger für Fehler anfälligen Satz an Funktionen erstellen, indem Sie Microsoft® .NET Framework verwenden.

CLR-benutzerdefinierte Funktionen
Bei CLR-benutzerdefinierten Funktionen handelt es sich um einfache statische Methoden (freigegebene Funktionen in Visual Basic), die innerhalb einer .NET-Assembly definiert werden. Um die SQLCLR-Objekte zu verwenden, müssen Sie die Assembly in SQL Server registrieren. Verwenden Sie hierfür die neue CREATE ASSEMBLY-Anweisung, und erstellen Sie anschließend jedes Objekt mit Verweis auf seine Implementierung innerhalb der Assembly. Die CREATE FUNCTION-Anweisung wurde für Funktionen erweitert, um die Erstellung von CLR-benutzerdefinierten Funktionen zu unterstützen. Um die Arbeit zu erleichtern, führt Visual Studio® 2005 alle Registrierungsprozesse für Sie aus, wenn Sie ein SQL Server-Projekt verwenden. Diese Projektart unterscheidet sich von den meisten Visual Studio-Projekten, weil das Projekt beim Debuggen (oder beim Starten ohne Debuggen) erneut kompiliert wird. Anschließend werden die entstehende Assembly sowie alle darin definierten SQLCLR-Objekte für SQL Server bereitgestellt und registriert. Die IDE führt dann das Testskript aus, das für das Projekt festgelegt wurde. Haltepunkte können sowohl in dem SQL-Skript als auch in Ihrem .NET-Code festgelegt werden und erleichtern das Debugging erheblich.
Das Hinzufügen einer Funktion entspricht dem Hinzufügen einer neuen Klasse zu einem beliebigen anderen Projekttyp. Fügen Sie dem Projekt einfach ein neues Element hinzu, und wählen Sie auf Aufforderung „Benutzerdefinierte Funktion“ aus. Die neue Methode wird zur partiellen Klasse hinzugefügt, die die gesamten Funktionen enthält. Ihre neue Methode wird ebenso über ein SqlFunction-Attribut verfügen, das auf sie angewendet wird. Sie wird von Visual Studio verwendet, um SQL-Anweisungen zu erstellen, die für die Registrierung der Funktion erforderlich sind. Die IsDeterministic-, IsPrecise-, DataAccess- und SystemDataAccess-Felder auf SqlFunction werden auch von SQL Server für verschiedene Zwecke verwendet.

Mustervergleich
Die einfachste Verwendung regulärer Ausdrücke besteht darin, zu bestimmen, ob eine Zeichenfolge mit einem Muster übereinstimmt. Wie Sie Abbildung 1 entnehmen können, ist dieser Vorgang ganz einfach.
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 ) );
    }
}

Zuerst verwenden Sie das Feld „Optionen“, um die Optionen für reguläre Ausdrücke der Funktionen zu speichern. In diesem Fall wurden RegexOptions.SingleLine und RegexOptions.IgnorePatternWhitespace bereits ausgewählt. Erstere gibt den Modus für eine Zeile an, und Letztere löscht Leerräume ohne Escapefrequenz aus dem regulären Ausdruck und aktiviert Kommentare, die mit einem Pfundzeichen markiert sind. Eine andere Option, die Sie möglicherweise nach eingehender Analyse verwenden möchten, ist die RegexOption.Compiled. Solange die Anzahl beschränkt ist, findet ein erheblicher Leistungszuwachs statt, wenn Sie für häufige Ausdrücke Compiled verwenden. Ausdrücke, die häufiger verwendet werden, sollten auf jeden Fall kompiliert werden. Für selten verwendete reguläre Ausdrücke sollte jedoch Compiled nicht verwendet werden, da die Startzeit verlängert und der Speicher-Overhead erhöht wird. Daher bietet es sich möglicherweise an, die allgemeine RegexMatch-Funktion mit einem zusätzlichen Parameter zu erweitern, der angibt, ob der Ausdruck kompiliert werden soll. Auf diese Weise können Sie von Fall zu Fall entscheiden, ob sich der zusätzliche Aufwand angesichts der resultierenden Leistungssteigerungen lohnt.
Nachdem Sie die zu verwendenden RegexOptions angegeben haben, definieren Sie die RegexMatch-Funktion, indem Sie den SqlChars-Datentyp anstelle von SqlString verwenden. Der SqlString-Datentyp übersetzt in nvarchar(4.000), während SqlChars in nvarchar(max) übersetzt. Mithilfe der neuen Funktion für maximale Größe können Zeichenfolgen die 8.000-Byte-Beschränkung von SQL Server 2000 überschreiten. In diesem Artikel wird nvarchar(max) so allgemein wie möglich verwendet, um höchste Flexibilität zu gewährleisten. Die Leistung kann jedoch mithilfe von nvarchar(4.000) erheblich verbessert werden, wenn alle relevanten Zeichenfolgen weniger als 4.000 Zeichen enthalten. Es empfiehlt sich, die jeweiligen Bedürfnisse zu überprüfen und den Code entsprechend zu formulieren.
Der übrige Code in der Methode ist einfach. Eine Regex-Instanz wird mit den definierten Optionen und dem bereitgestellten Muster erstellt. Anschließend wird die IsMatch-Methode verwendet, um zu bestimmen, ob die angegebene Eingabe mit dem Muster übereinstimmt. Jetzt müssen Sie dem Testskript eine einfache Abfrage hinzufügen:
select dbo.RegexMatch( N'123-45-6789', N'^\d{3}-\d{2}-\d{4}$' )
Das Muster in dieser Anweisung stellt einen einfachen Test für eine US-amerikanische Sozialversicherungsnummer dar. Legen Sie einen Haltepunkt für die neue Abfrage fest, und starten Sie dann das Debugging, um die Funktion schrittweise zu untersuchen. Mit dieser Funktion können Sie viele verschiedene Tests durchführen. Im Folgenden erhalten Sie jedoch einen Überblick über besondere Aspekte, die häufig nicht berücksichtig werden. Es ist beispielsweise sehr wichtig, konsistente Namenskonventionen innerhalb einer Datenbank einzuhalten. Jedoch ist es schwierig, eine Abfrage zu schreiben, die alle gespeicherten Prozeduren daraufhin überprüfen soll, ob die Richtlinien Ihrer Organisation eingehalten werden. Mithilfe der RegexMatch-Funktion können Sie diese Aufgabe ganz einfach durchführen. Beispielsweise führt der folgende Abfragentest diese Aufgabe durch:
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
    and dbo.RegexMatch( ROUTINE_NAME, 
        N'^usp_(Insert|Update|Delete|Select)([A-Z][a-z]+)+$' ) = 0
Diese Abfrage überprüft, ob jede gespeicherte Prozedur mit dem Präfix „usp_“ beginnt, gefolgt von „Insert“, „Update“, „Delete“ oder „Select“ (Einfügen, Aktualisieren, Löschen oder Auswählen) sowie mindestens einem Entitätsnamen. Sie überprüft zudem, ob jedes Wort in der Entität mit einem Großbuchstaben beginnt. Vergleichen Sie die vier Zeilen mit dieser vereinfachten Version, in der nur integrierte Funktionen verwendet werden:
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
    and ( LEN( ROUTINE_NAME ) < 11
    or LEFT( ROUTINE_NAME, 4 ) <> N'usp_'
    or SUBSTRING( ROUTINE_NAME, 5, 6 ) not in 
        ( N'Insert', N'Update', N'Delete', N'Select' ) )
Obwohl mehr Code vorhanden ist, fehlen in dieser Abfrage mehrere Funktionen, die in der Version mit regulären Ausdrücken vorhanden sind. Erstens wird die Groß-/Kleinschreibung nicht beachtet. Darüber hinaus entsteht durch die Verwendung von Sortierungen innerhalb der Abfrage zum Durchführen der Tests eine nicht normgerechte Abfrage. Zweitens wird der tatsächliche Entitätsname im Prozedurennamen nicht überprüft. Das dritte Problem besteht darin, dass die vier in der Abfrage getesteten Zeichenfolgen aus sechs Zeichen bestehen. Dadurch war es möglich, den Code durch Entnahme einer einzelnen untergeordneten Zeichenfolge mit sechs Zeichen zu vereinfachen. Anschließend konnte diese Zeichenfolge mit jedem akzeptablen Vorgang verglichen werden. Dies ist in diesem Beispiel nicht besonders problematisch, da alle Vorgangsnamen aus sechs Zeichen bestehen. In einem Standard, der komplexere Verben wie „Get“, „List“ oder „Find“ angibt, ergibt sich ein ganz anderes Bild. Diese Verben können einfach von der RegexMatch-Funktion bearbeitet werden, weil sie nur zusätzliche Alternativen in der Liste darstellen.
Reguläre Ausdrücke werden häufig für Prüfungen verwendet, wie für Telefonnummern, Postleitzahlen oder benutzerdefinierte Kontonummernformate. Die folgende Tabellendefinition zeigt, dass die CHECK-Einschränkung bestens dafür geeignet ist.
CREATE TABLE [Account]
(
    [AccountNumber] nvarchar(20) CHECK (dbo.RegexMatch( 
        [AccountNumber], '^[A-Z]{3,5}\d{5}-\d{3}$' ) = 1),
    [PhoneNumber] nchar(13) CHECK (dbo.RegexMatch( 
        [PhoneNumber], '^\(\d{3}\)\d{3}-\d{4}$' ) = 1),
    [ZipCode] nvarchar(10) CHECK (dbo.RegexMatch( 
        [ZipCode], '^\d{5}(\-\d{4})?$' ) = 1)
)
Die AccountNumber-Spalte wird anhand einer willkürlichen Konvention überprüft, die mit drei bis zu fünf Buchstaben beginnt, auf die fünf Zahlen, ein Gedankenstrich und schließlich noch drei weitere Zahlen folgen. Sowohl die Telefonnummer als auch die Postleitzahlen wurden anhand von US-amerikanischen Standardtelefonnummern und Postleitzahlenformaten überprüft. Während die RegexMatch-Funktion SQL Server viele Funktionen bietet, wird im nächsten Abschnitt gezeigt, dass die Implementierung regulärer Ausdrücke in .NET diese Möglichkeiten sogar übertrifft.

Extrahieren von Daten
Die Gruppierungsfunktionen regulärer Ausdrücke können verwendet werden, um Daten aus einer Zeichenfolge zu extrahieren. Die RegexGroup-Funktion stellt T-SQL diese Funktionalität zur Verfügung:
[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;
}
Diese Funktion erstellt wie die RegexMatch-Funktion ein Regex-Objekt. Es wird keine Prüfung auf eine Entsprechung durchgeführt, sondern es wird ein Match-Objekt für die erste in der Eingabezeichenfolge gefundene Entsprechung erstellt. Das Match-Objekt wird verwendet, um die angegebene Gruppe abzurufen. Befindet sich keine Entsprechung in der Eingabe, wird ein NULL-Wert zurückgegeben. Diese Funktion funktioniert auch, wenn Sie nummerierte Gruppen anstatt benannter Gruppen verwenden. Übergeben Sie einfach einen ganzzahligen Wert an die Funktion innerhalb Ihres SQL-Codes. Er wird implizit zu nvarchar umgewandelt, und die entsprechende Gruppe wird zurückgegeben.
Sie können die RegexGroup-Funktion innerhalb einer SELECT-Liste verwenden, um bestimmte Informationen aus einem anderen Datenelement zu extrahieren. Wenn Sie eine Spalte mit einer gespeicherten URL hätten, könnten Sie jetzt unkompliziert die URL analysieren, um einzelne Elemente zu bestimmen. Diese Abfrage verwendet Gruppierungen, um jeden einzelnen in der Spalte „URL“ der Tabelle „UrlTable“ gespeicherten Server zu bestimmen.
select distinct dbo.RegexGroup( [Url],
    N'https?://(?<server>([\w-]+\.)*[\w-]+)', N'server' )
from [UrlTable]
Sie können diese Funktion ebenso innerhalb berechneter Spalten verwenden. Die folgende Tabellendefinition teilt E-Mail-Adressen in Postfach und Domäne auf.
CREATE TABLE [Email]
(
    [Address] nvarchar(max),
    [Mailbox] as dbo.RegexGroup( [Address], 
        N'(?<mailbox>[^@]*)@', N'mailbox' ),
    [Domain] as dbo.RegexGroup( [Address], N'@(?<domain>.*)', N'domain' )
Die Postfachspalte gibt das Postfach oder den Benutzernamen der E-Mail-Adresse zurück. Die Domänenspalte gibt die Domäne der E-Mail-Adresse zurück.

Musterspeicherung
Alle von diesen Funktionen verwendeten Muster sind lediglich Zeichenfolgen. Folglich können sie alle in einer Tabelle in Ihrer Datenbank gespeichert werden. Die meisten Datenbanken mit internationalen Daten verfügen über eine Tabelle, die Länder darstellt. Indem Sie dieser Tabelle einige zusätzliche Spalten hinzufügen, können Sie länderspezifische Prüfmuster speichern. Dadurch könnte die auf eine Adresszeile angewendete Einschränkung je nach Land der Zeile variieren.
In Datenbanken, die Daten für Clients speichern, ist in der Regel bereits eine Tabelle vorhanden, die einen Client darstellt. Diese Tabelle kann zum Speichern von Gruppierungsmustern verwendet werden. Mit diesen Mustern können Sie die Methode beschreiben, mit der rohe Clientdaten in der Datenbank gespeichert werden. Zudem können Sie berechnete Spalten erstellen, um die Daten zu entnehmen, die Sie tatsächlich aus den Clientdaten benötigen. Wenn beispielsweise alle Ihre Clients einmalige Schemata für Kontonummern haben und Sie nur bestimmte Teile dieser Kontonummer benötigen, können Sie leicht einen Ausdruck erstellen, der die richtige Information für jeden Client entnimmt.

Entsprechungen
Anstatt zu bestimmen, ob eine Zeichenfolge mit einem Muster übereinstimmt, ist es manchmal wünschenswert, jede Entsprechung zu extrahieren. Vorher wäre es für diese Art der Extrahierung erforderlich gewesen, dass der Cursor Abschnitte einer Zeichenfolge durchläuft. Dieser Prozess ist langsam, und der Code kann nur schwer verstanden und gepflegt werden. Reguläre Ausdrücke stellen für die Durchführung dieses Vorgangs ein geeigneteres Mittel dar. Es stellt sich das Problem, wie alle erforderlichen Daten innerhalb eines SQL-Konstrukts zurückgegeben werden können. Hierfür können Tabellenfunktionen verwendet werden.
Tabellenfunktionen ähneln den vorherigen Funktionen, weisen jedoch die beiden folgenden erheblichen Unterschiede auf. Erstens müssen die auf die Methode angewandten Attribute die Struktur der zurückgegebenen Tabelle vollständig deklarieren. Zweitens sind zwei Methoden beteiligt. Die erste Methode gibt ein aufzählbares Objekt anstatt des eigentlichen Ergebnisses der Funktion zurück. Mit der zweiten Methode werden die aufgelisteten Objekte übergeben, um die Felder jeder Zeile aufzufüllen. Jeder über den Enumerator abgerufene Wert sollte einer Zeile des Resultsets entsprechen. Die ICollection-Schnittstelle im .NET Framework implementiert IEnumerable. Das bedeutet, dass jede Sammlung mit der ersten Methode zurückgegeben werden kann. Die Regex-Klasse enthält eine Matches-Methode, die eine MatchCollection zurückgibt, die Sie verwenden könnten. Ein Problem bei der MatchCollection ist, dass die gesamte Zeichenfolge verarbeitet werden muss, bevor die Matches-Methode zurückgegeben wird. SQL Server enthält Optimierungen, die von der Verarbeitung bei Bedarf abhängen. Aus diesem Grund ist es vorzuziehen, einen eigenen Enumerator zu schreiben, der jede Entsprechung auf Anforderung zurückgibt, anstatt vorab die gesamte Sammlung zurückzugeben. Diese Entscheidung hängt letztendlich davon ab, wie die Funktion verwendet wird. Sie sollte vor der Optimierung des Enumerators eingehend getestet werden.
Der Code in Abbildung 2 zeigt den Enumerator. Die MatchNode-Klasse dient als Wrapper einer einzelnen Entsprechung in der Zeichenfolge und verfolgt die Position innerhalb des Satzes der zurückgegebenen Entsprechungen. Die MatchIterator-Klasse ist aufzählbar und verarbeitet die reguläre Ausdrucksverarbeitung. Sie verwendet das neue yield-Schlüsselwort, um den Enumerator viel unkomplizierter als vorherige Framework-Versionen zu erstellen. Sie gibt jede Entsprechung zurück, die in der Eingabezeichenfolge wie angefordert erkannt wird.
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);
    }
}

Der Code in Abbildung 3 definiert die Tabellen-CLR-UDF. Die RegexMatches-Methode gibt einen neuen MatchIterator zurück. Das SqlFunctionAttribute der RegexMatches-Methode enthält ebenso einige zusätzliche Eigenschaften. Die TableDefinition-Eigenschaft ist auf die Tabellendefinition der Funktion eingestellt. Der FillRowMethodName ist auf den Namen der Methode eingestellt, die jede Iteration des aufzählbaren zurückgegebenen Objekts aufruft. In diesem Fall lautet die Methode FillMatchRow.
[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( ) );
}

Für jede Iteration von MatchIterator wird ein MatchNode an die FillMatchRow-Methode als erstes Argument übergeben. Die übrigen Parameter der FillMatchRow-Methode müssen als Ausgabeparameter deklariert sein und der in der ersten Funktion definierten Tabellendefinition entsprechen. Die FillMatchRow-Funktion verwendet einfach die Eigenschaften von MatchNode, um die Felderdaten aufzufüllen.
Mit dieser Funktion können Sie schließlich auf unkomplizierte Weise mehrere Datenelemente aus einer Zeichenfolge extrahieren. Um die Verwendung der RegexMatches-Funktion zu illustrieren, wird im Folgenden eine Zeichenfolge verarbeitet, um mithilfe dieser Abfrage zu bestimmen, wie viele unterschiedliche Wörter enthalten sind:
declare @text nvarchar(max), @pattern nvarchar(max)
select
    @text = N'Here are four words.',
    @pattern = '\w+'
select count(distinct [Text])
    from dbo.RegexMatches( @text, @pattern )
Dieses Beispiel ist ziemlich einfach. Es zeigt das Potential für die Verwendung der Funktion. Durch Entfernen des eindeutigen Schlüsselworts wird jedoch die gesamte Wortzahl einer Zeichenfolge zurückgegeben. Es gibt viele Websites, die Texteingaben auf eine scheinbar willkürliche Länge beschränken. Dieser Test in Kombination mit der neuen nvarchar(max.)-Notation ermöglicht es, die Eingabe stattdessen auf eine bestimmte Wortzahl zu beschränken. Diese Art von Abfrage kann für verschiedene analytische Verarbeitungsanforderungen verwendet werden. Die RegexMatches-Funktion kann jedoch ebenso für gängigere Aufgaben verwendet werden. Leider werden durch diese Art von Abfrage reguläre Ausdrücke übermäßig verwendet. Der durch den Ausdruck „\w+“ durchgeführte Trennungsvorgang könnte in diesem Fall genauso leicht mithilfe der String.Split-Methode durchgeführt werden, was sehr viel schneller wäre. Reguläre Ausdrücke sind ein sehr leistungsfähiges Tool. Stellen Sie jedoch sicher, dass Sie sie nur aus wichtigen Gründen verwenden: Es gibt möglicherweise einfachere Tools, die für Sie in bestimmten Fällen eine bessere Leistung erbringen würden.
In MSDN®-Foren werden häufig Fragen dazu gestellt, wie eine Liste mit Werten an eine gespeicherte Prozedur übergeben werden kann. Es existieren darüber hinaus verschiedene unübersichtliche Methoden zum Analysieren einer derartigen Liste in eine tatsächliche Liste, um die korrelierten Datensätze zu bestimmen. Die RegexMatches-Funktion bietet eine sehr viel übersichtlichere Vorgehensweise.
declare @pattern nvarchar(max), @list nvarchar(max)
select @pattern = N'[^,]+', @list = N'2,4,6'

select d.* from [Data] d
inner join dbo.RegexMatches( @list, @pattern ) re
    on d.[ID] = re.[Text]
Das Muster entspricht einer beliebigen Gruppe von Zeichen, die kein Komma enthält. Diese Abfrage gibt jeden in der Liste identifizierten Datensatz zurück, wenn eine Tabelle mit dem Namen Date mit einer Spalte mit ganzen Zahlen namens ID vorhanden ist. Dies wird bei der Entscheidung über die impliziten Umwandlungsfunktionen innerhalb von SQL Server von Nutzen sein. Die gleiche Abfrage kann für eine ganze Zahl, Datum/Zeit, GUID oder Gleitkommadatentypen verwendet werden. Andere Methoden der Verarbeitung einer Liste mit Werten benötigen mehrere Funktionen oder gespeicherte Prozeduren, um eine vergleichbare Flexibilität zu bieten. Diese Funktion kann auch für Listen verwendet werden, die nicht durch Kommas getrennt sind. Eine Liste, die durch Leerzeichen, Semikola, Tabstopps, Zeilenumbrüche oder andere identifizierbare Zeichen getrennt wird, kann verarbeitet werden.

Extrahieren von Daten innerhalb von Entsprechungen
Ähnlich wie beim Zurückgeben von Entsprechungen können Daten aus jeder Entsprechung extrahiert werden. Mit SQL ist diese Aufgabe kompliziert. Normalerweise würde diese Art Aufgabe innerhalb einer Anwendung und nicht in der Datenbank implementiert werden. Dadurch entstehen Probleme, weil jede die Datenbank verwendende Anwendung die erforderliche Verarbeitung implementieren müsste. In solchen Szenarien wäre es sinnvoll, diese Funktionalität innerhalb gespeicherter Prozeduren zu implementieren.
Wie bei der RegexMatches-Implementierung empfiehlt es sich, ein benutzerdefiniertes aufzählbares Objekt zur Rückgabe von Gruppeninformationen zu verwenden. Das Gruppieren ist nur unwesentlich komplizierter, weil darüber hinaus Gruppen innerhalb jeder Entsprechung durchlaufen werden müssen. In Abbildung 4 entspricht die GroupNode-Klasse der MatchNode-Klasse. Sie enthält jedoch zusätzlich den Namen der Gruppe, die sie darstellt. Die GroupIterator-Klasse ist mit der MatchIterator-Klasse vergleichbar, enthält jedoch für die Rückgabe jeder Gruppe eine zusätzliche Schleife. Jetzt haben Sie ein aufzählbares Objekt und können wie für die RegexMatches-Funktion eine Tabellenfunktion definieren.
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);
    }
}

In Abbildung 5 wurde die RegexGroups-Funktion wie die RegexMatches-Funktion definiert. Der einzige Unterschied besteht jedoch darin, dass die RegexGroups-Funktion eine zusätzliche Spalte mit Daten zurückgibt, die den Namen der Gruppe innerhalb der Entsprechung enthält. Mit dieser Funktion können Sie jetzt mehrere Entsprechungen innerhalb einer Zeichenfolge suchen und bestimmte Informationen aus jeder Entsprechung extrahieren.
[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( ) );
}
Bei der Arbeit mit Datenbanken kommt es häufig vor, dass Daten in unterschiedlichen Formaten importiert werden müssen. Das Importieren von Dateien in ein durch Kommas getrenntes Format ist mühsamer, als es sein sollte. Die meisten Entwickler erstellen eine Anwendung, die jede Zeile verarbeitet, Daten extrahiert und anschließend für jede Zeile eine gespeicherte Prozedur ausführt. Obwohl dieser Prozess funktioniert, möchte ich eine andere Lösung vorschlagen. Wie wäre es, wenn Sie die gesamte Datei an eine gespeicherte Prozedur übergeben könnten, die den vollständigen Prozess verarbeitet? Diese Idee wird in der Regel als zu kompliziert verworfen. Mit der RegexGroups-Funktion können Sie diesen Einfügevorgang jedoch mithilfe einer einzigen Abfrage durchführen. Betrachten Sie beispielsweise die folgenden Kundendaten.
2309478,Janet Leverling,J
2039748,Nancy Davolio,N
0798124,Andrew Fuller,M
4027392,Robert King,L
Aus jeder Zeile benötigen Sie drei unterschiedliche Informationen: Die siebenstellige Kundennummer, den Kundenamen und den aus einem Zeichen bestehenden Kundentyp. Mit dem folgenden Ausdruck können Sie alle drei Informationen extrahieren.
(?<CustomerNumber>\d{7}),(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n
Leider können Sie die durch die RegexGroups-Funktion zurückgegebenen Ergebnisse jedoch nicht direkt verwenden. Anstatt einen Cursor zum Durchlaufen der Ergebnisse zu verwenden, können Sie die Pivotfunktionalität in SQL Server 2005 nutzen. Wenn Sie nun alle Informationen in einer gespeicherten Prozedur zusammenfassen, haben Sie alles, was Sie brauchen. Die gespeicherte Prozedur in Abbildung 6 akzeptiert den Text einer gesamten durch Kommas getrennten Datei bis zu 2 GB an Unicode-Daten. Sie verarbeitet die ganze Datei und fügt jede Zeile der Datei als eine Zeile in die Kundentabelle ein. Jede durch Trennzeichen getrennte Textdatei könnte auf die gleiche Weise verarbeitet werden. Escape-Zeichenfolgen könnten mit kleinen Änderungen am Muster hinzugefügt werden, um Kommas innerhalb von Zeichenfolgen zu unterstützen.
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

Dieses Verfahren zeigt jedoch auch, dass jede Aufgabe auf unterschiedliche Art und Weise durchgeführt werden kann und reguläre Ausdrücke nicht immer die beste Wahl sind. In diesem Beispiel werden durch die Verwendung eines Pivots alle Arbeitsschritte rückgängig gemacht, die von RegexGroups durchgeführt wurden, um die Daten im speziellen zusammengefassten Format zurückzugeben. Mithilfe einer einfacheren und schnelleren Tabellenwertfunktion (Table Valued Function, TVF), die bereits jede Zeile gelesen, String.Split auf Kommas angewendet und jede Zeile zurückgegeben hat, könnten Sie die Daten direkt in die Tabelle einfügen.

Schlussbemerkung
Obwohl diese vergleichenden Funktionen sehr leistungsfähig sind, sind sie nicht vollkommen. Es gibt viele Möglichkeiten, mit denen die exakte Methode zum Durchführen von Vergleichen bestimmt werden kann. Wenn Ihre Datenbanksortierung nicht zwischen Groß- und Kleinschreibung unterscheidet, sollte diese Unterscheidung auch nicht von den Funktionen beim Vergleichen vorgenommen werden. Zur Reduzierung einiger Resultsets wird möglicherweise die Aufzeichnungsoption benötigt. Die mehrzeilige Option kann Ihnen für einige Aufgaben das Erstellen präziserer Muster ermöglichen. Sie möchten möglicherweise einen benutzerdefinierten Typ erstellen, um die exakten erforderlichen Optionen an jede der Funktionen zu übergeben. Dadurch könnte bei jeder Ausführung einer Funktion ein anderer Satz an Optionen verwendet werden.
Denken Sie auch daran, dass bei der Textverarbeitung Lokalisierungsprobleme auftreten können. Beispielsweise berücksichtigt die .NET Framework Regex-Klasse viel mehr Zeichen als die lateinischen im Beispiel. Aus diesem Grund sollten Sie beim Entwickeln von Mustern für Datenbanken mit internationalen Daten vorsichtig sein.
Darüber hinaus empfiehlt es sich, genau abzuwägen, ob die ernorme Leistungsstärke regulärer Ausdrücke tatsächlich benötigt wird. Einige Aufgaben können schneller und unkomplizierter mit einfacheren Toolsets durchgeführt werden.
Aus Gründen der Einfachheit wurden in den angeführten Beispielen die Überprüfung und Fehlerbehandlung nicht behandelt. Diese sollten jedoch in alle Produktionssysteme aufgenommen werden. Alle Funktionseingaben sollten geprüft werden. Die Antworten auf Null oder leere Zeichenfolgeneingaben sollten durch Ihre Anforderungen bestimmt werden. Die Regex-Klasse kann Ausnahmen auslösen, wenn das Muster nicht analysiert werden kann oder die Optionen ungültig sind. Diese Ausnahmen müssen korrekt behandelt werden.
Die Kombination aus regulären Ausdrücken und SQL bietet viele alternative Methoden zur Datenverarbeitung. Durch Verwendung dieser Funktionen kann der Zeitaufwand reduziert werden, der für das Hinzufügen von Funktionalitäten zur Datenbank sowie für die übersichtlichere Verwaltung des Systems erforderlich ist. Jede Datenbank kann reguläre Ausdrücke verwenden. Es empfiehlt sich, mit diesen Funktionen zu experimentieren, um neue und sogar noch kreativere Verwendungen zu ermitteln.

David Banister ist Senior-Softwareentwickler bei einer der größten Buchhaltungsfirmen in Atlanta. Er entwickelt seit vielen Jahren Software. In seiner Freizeit liest er gerne ECMA-Sprachspezifikationen, spielt Tennis und unterstützt lokale Bands.

Page view tracker