Genau das können mit .NET programmierte benutzerdefinierte Funktionen leisten. Schließlich bietet das .NET Framework eine Klasse RegularExpressions, die genau die hierfür benötigten Methoden enthält.
Das Problem
Wie erstelle ich eine benutzerdefinierte Funktion mit .NET Code, um sie anschließend im SQL Server zu verwenden?
Die Lösung
- Erstellen Sie in Visual Studio ein Projekt vom Typ "SQL Server Projekt". Dafür benötigen Sie Visual Studio Professional - die mit SQL Server ausgelieferte Version "Business Intelligence Development Studio" kann das nicht.
- Veröffentlichen Sie die Funktion im SQL Server
- Nutzen Sie diese Funktion in Ihren SQL Statements.
Erweitern Sie nun diesen Rahmen:
- Fügen Sie den Namespace System.Text.RegularExpressions hinzu. Darin sind die Methoden zum Verarbeiten regulärer Ausdrücke vorhanden.
- Wir verwenden in diesem Beispiel die Methode Match(), um zu überprüfen, ob der Parameter matchString dem Muster pattern entspricht.
Das Veröffentlichen der Funktion erledigt Visual Studio vollautomatisch. Hinter den Kulissen führt es dabei folgende Schritte aus:
- Es kompiliert die DLL-Datei (Assembly) mit dem CLR-Code der neu erstellten Klasse UserDefinedFunctions.
- Es lädt diese Assembly wird in den SQL Server:
CREATE ASSEMBLY <name der klasse> FROM <dateipfad> - Dann deklariert es die Methode udf_RegExMatch als Funktion, deren Programmcode in dieser Assembly liegt:
CREATE FUNCTION udf_RegExMatch (@pattern nvarchar(4000), @matchString nvarchar(4000))
RETURNS int
AS EXTERNAL NAME <name der methode>
select * from sys.assembly_files
select * from sys.assembly_modules
Danach finden Sie diese Funktion im SQL Server Management Studio (Object Explorer) unter "Programmierbarkeit" - "Funktionen". Bevor Sie diese Funktion testen können, muss zunächst die Ausführung von .NET Code zugelassen werden. Das ist standardmäßig nicht erlaubt.
exec sp_configure 'clr enabled', 1;
reconfigure
Sie können die Funktion nun testen, zum Beispiel mit folgendem Statement:
SELECT [DemoDB].[dbo].[udf_RegExMatch] (
'[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|de)'
,'v.heck@netcologne.de')
Das Select Statement liefert den Wert 1 zurück, wenn die E-Mail Adresse v.heck@netcologne.de dem vorgegebenen Muster entspricht; ansonsten liefert sie den Wert 0 zurück. Probieren Sie es aus!
Mit dem folgenden Statement beispielsweise können Sie alle E-Mail Adressen in der Tabelle Person.EmailAddress in der AdventureWorks2008R2 Datebank überprüfen:
select
EmailAddress,
DemoDB.dbo.udf_RegExMatch
('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|de)',
EmailAddress)
from AdventureWorks2008R2.Person.EmailAddress
Fazit
Anhand dieses Beispiels ließ sich zeigen, wie eine Funktion in .NET programmiert werden kann, die anschließend als Funktion im SQL Server zur Verfügung steht. Wie so oft beginnt die eigentliche Arbeit im Projekt dort, wo diese Demo aufhört. Insbesondere haben wir noch keinerlei Fehlerbehandlung hinzugefügt. Und auch die Performance-Aspekte haben wir bisher außer acht gelassen. Denn so, wie wir die Aufgabenstellung bisher gelöst haben, wird bei jedem Aufruf der Funktion die Regex neu instanziiert. Bei größeren Datenmengen (siehe zweites SELECT Statement) kann das zu einem echten Performance-Engpass werden.
Mein Ziel war es, hier das Prinzip aufzuzeigen. Weiter führende Informationen finden Sie zum Beispiel hier:
http://msdn.microsoft.com/de-de/magazine/cc163473.aspx
http://blogs.msdn.com/b/sqlclr/archive/2005/06/29/regex.aspx
