Reguläre Ausdrücke in Oracle 10g – Waren Queries bis jetzt

Donnerstag, 11. November 2004
13h00, Mozartsaal
Reguläre Ausdrücke in Oracle 10g –
Waren Queries bis jetzt irregulär?
Dr. Martin Wunderli
Trivadis AG, Zürich-Glattbrugg
Schlüsselworte
Reguläre Ausdrücke, Pattern Matching, SQL Queries
Zusammenfassung
Pattern-Matching in SQL – die Suche nach Zeichenketten, die einem bestimmten Muster genügen – wird mit Oracle 10g durch Verwendung von regulären
Ausdrücken auf eine neue Stufe gehoben. Zum einen sind diese mächtiger als
die eher rudimentären Wildcards der LIKE Funktion, zum anderen sind sie performanter als das OWA_PATTERN Package aus Oracle8i. UNIX Administratoren
und Shell Programmieren werden sich damit sehr schnell zurechtfinden, für
reine Oracle DBAs und Entwickler ohne RegExp Praxis erfodert deren Gebrauch
erfahrungsgemäss etwas Eingewöhnungszeit.
Theoretischer Hintergrund
Den theoretischen Hintergrund zu regulären Ausdrücken liefert die Berechenbarkeitstheorie, ein Teilgebiet der theoretischen Informatik.
Reguläre Ausdrücke bilden eine Familie von kompakten (manche mögen
sagen: kryptischen) aber leistungsfähigen formalen Sprachen, mit denen sich
Mengen von Zeichenketten beschreiben lassen. Die regulären Ausdrücke werden von vielen Programmen und Programmiersprachen verwendet, um Muster
zu definieren, nach denen Zeichenketten in Texten zu suchen und eventuell
durch etwas anderes zu ersetzen sind.
Formale Sprachen werden durch eine Menge von zulässigen Zeichenketten
oder Treffern beschrieben und sind über einem Alphabet definiert.
Eine formale Sprache über dem Alphabet {a,b,c} könnte z.B. die Menge aller
Worte sein, die mit einer Folge von a beginnen, mit einer Folge von b weitergehen und mit einer Folge von c enden. 'aaaabc' wäre dann ein Wort dieser
Sprache, 'aaaac' ebenfalls, 'ca' aber nicht.
Diese formale Sprache lässt sich durch den regulären Ausdruck 'a*b*c*' (* steht
hier für die 0-n malige Wiederholung des vorherigen Zeichens) beschreiben.
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Historie von Pattern Matching bis Oracle 10g
Bis Oracle 8.0 einschliesslich standen nur die Funktion LIKE mit den Wildcards
'%' (null bis n beliebige Zeichen) und '_' (genau ein beliebiges Zeichen) für
Pattern-Matching in SQL-Statements zur Verfúgung.
In Oracle8i kam das Package OWA_PATTERN hinzu, welches das Suchen mit
regulären Ausdrücken implementiert. Dieses hat aber noch einige Nachteile:
• Es ist relativ langsam
• Von seiner Struktur ist es mehr für PL/SQL geeignet, da die meisten Prozeduren mit in/out Parametern arbeiten
• Es implementiert reguläre Ausdrücke nicht vollständig
Oracle 10g implementiert nun das Pattern-Matching mit regulären Ausdrücke
nach Posix Standard (Extended Regular Expressions – ERE) direkt als SQL Funktionen. Es handelt sich hier um die Funktionen
• REGEXP_LIKE: Liefert TRUE, falls ein Attribut einer RegExp genügt
• REGEXP_REPLACE: Ersetzt Teilzeichenketten, falls sie der RegExp genügen
• REGEXP_INSTR: Liefert die Position einer Teilzeichenkette, die der RegExp
genügt
• REGEXP_SUBSTR: Liefert die Teilzeichenkette, die der RegExp genügt
Grundbegriffe von regulären Ausdrücken
Der vorliegende Beitrag soll keine Schulung der vielfältigen Möglichkeiten der
Darstellung von regulären Ausdrücken sein, sind sie zu umfangreich. Wir verweisen dazu vielmehr auf die Literaturliste.
Wir wollen aber die zentralen Mittel auflisten, die zur Darstellung eines regulären Ausdrucks, zur Definition des Musters, nach dem wir suchen, verwendet
werden können, um die Grundlage für die nachfolgenden Beispiele zu erhalten.
Ein regulärer Ausdruck besteht aus Buchstaben des verwendeten Zeichensatzes
sowie eventuell Symbole mit besonderer Bedeutung. Die Zeichenkette 'abc' ist
damit schon ein regulärer Ausdruck, auf den das Wort 'abc' passt. Allerdings
auch nur dieses eine Wort.
Damit ein regulärer Ausdruck auf mehr als eine Zeichenkette passt, sind unter
anderem folgende Symbole verwendbar:
Symbol
*
?
+
{4}
{3,6}
.
[0-9A-F]
[:alnum:]
()
\2
(xx|yy)
^
$
Bedeutung
0 bis n malige Wiederholung des vorangehenden Ausdrucks
0 oder 1-malige Wiederholung des vorangehenden Ausdrucks
1 bis n malige Wiederholung des vorangehenden Ausdrucks
Genau 4-malige Wiederholung des vorangehenden Ausdrucks
3 bis 6-malige Wiederholung des vorangehenden Ausdrucks
Ein belibiges Zeichen
Genau ein Zeichen aus der Menge 0-9 und A-F
Zeichenklasse, hier alle alphanummerischen Zeichen
(Buchstaben und Ziffern)
Gruppierung
Referenzierung einer Gruppierung, hier der zweiten
Entweder die Zeichenkette xx oder yy
Der Beginn einer Zeile
Das Ende einer Zeile
Will man die Symbole als 'normale' Zeichen verwenden, müssen sich durch ein
ESCAPE Zeichen (typischerweise '\') geschützt werden.
Die RegExp 'Bericht[0-9][0-9].txt' passt z.B. auf Bericht04.txt, aber nicht auf
Bericht4.txt und auch nicht auf Bericht1994.txt.
Beispielanwendungen
Verifikation von Email Adressen:
Wir wollen testen, ob eine Email Adresse auf .COM oder .DE endet und ob ein
'@' enthalten ist:
SQL> SELECT address
2 FROM email
3 WHERE regexp_like (address, '.*@.*\.(com|de)');
ADDRESS
[email protected]
[email protected]
[email protected]
Erklärung:
• .* ist eine beliebige Zeichenkette
• Dann folgt das @ Zeichen
• .* ist wieder eine beliebige Zeichenkette
• \. Den Punkt müssen wir escapen, sonst steht er für ein beliebiges Zeichen
• (com|de) Enden darf die Zeichenkette entweder mit com oder de
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Man könnte der Funktion noch einen dritten parameter i für die Case insensitive Suche mitgeben.
Check Condition
Da die regexp Funktionen in Check Contraints verwendet werden dürfen, kann
man sie sehr gut für die Validierung von Daten einsetzen.
Beispiel: Validierung einer Artikel-Nummer mit dem Format x99.999.999
wobei
• x ein Grossbuchstabe zwischen A und F
• 9 eine Ziffer
sein soll. Die entsprechende SQL Syntax sieht so aus:
CREATE TABLE parts
(partno VARCHAR2(11) NOT NULL
CONSTRAINT check_part_no CHECK
(regexp_like(partno,
'[A-F][[:digit:]]{2}\.[[:digit:]]{3}\.[[:digit:]]{3}'))
)
Erklärung:
• [A-F] Zuerst genau ein Zeichen aus der Menge A-F,
• [[:digit:]]{2} dann genau 2 Ziffern,
• \. gefolgt von einem Punkt (Escape nicht vergessen!),
• [[:digit:]]{3} genau 3 Ziffern
• \. ein Punkt
• [[:digit:]]{3} und zum Schluss wieder drei Ziffern.
Ich mag als UNIX-ler etwas voreingenommen sein, aber obiges in SQL?
Suchen und Ersetzen
Auch Suchen/Ersetzen ist mit regulären Ausdrücken möglich. Im Zielausdruck
wird dabei mit \n auf Gruppieren aus dem Suchausdruck verweisen. Ein Beispiel: Wir wollen Mailadressen der Form [email protected]
nach [email protected] umformatieren.
In SQL ist das relativ unübersichtlich und kompliziert:
SELECT substr(address,
instr (address, '.') + 1,
instr (address, '@') - instr (address, '.') - 1
) || '.'
|| substr (address, 1, instr (address, '.') - 1)
|| substr (address, instr (address, '@'))
FROM email
Mittels regulären Ausdrücken wesentlich kompakter und einfacher:
SELECT address,
regexp_replace (address, '(.*)\.(.*)@(.*)', '\2.\1@\3')
FROM email
Erklärung:
• Wichtig ist hier, dass Teilzeichenketten mittels () gruppiert und damit referenziert werden können
• Die erste und die zweite gefundene Zeichenkette (vor und nach dem Punkt)
werden in umgekehrter Reihenfolge ausgegeben
Globalization Support
Oracle hat die regulären Ausdrücke von Anfang an NLS-ready implementiert.
Um nur einige Beispiele zu nennen:
• Die regulären Ausdrücke matchen auf Zeichen, nicht auf Bytes. Dadurch entstehen keine Probleme mit Multi-Byte Zeichensätzen.
• Ebenso sind die Zeichenklassen sensitiv bezüglich der zugrunde liegenden
Unicode Zeichensätze. So enthält [:digit:] eventuell Arabisch-Indische oder
Bengalische Spezialzeichen
• Sind der reguläre Ausdruck und die untersuchte Zeichenkette in verschiednen
Zeichensätzen abgelegt, so wird der Ausdruck konvertiert
Performance
Jeder kennt vermutlich den Spruch 'Wer misst, misst Mist!'. Nichtsdestotrotz
hier ein kleines Beispiel, welches aber durchaus typisch ist und die Performance
der regulären Ausdrücke grob einordnen kann.
Getestet wurde auf einer Tabelle MY_ALL_OBJECTS, welche durch mehrfache
Duplikation auf sich selbst aus der Spalte OBJECT_NAME der Tabelle
.SYS.ALL_OBJECTS entstand. Total enthält sie 1.3 Mio Rows.
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Zuerst das OWA_PATTERN Package. Da MATCH mit IN/OUT Parametern arbeitet, verwenden wir AMATCH, welche das erste Auftreten findet:
SQL> select count(*) from my_all_objects where
owa_pattern.amatch(object_name,1,'A') > 0;
COUNT(*)
---------27648
Elapsed: 00:02:29.14
Mit regulären Ausdrücken:
select count(*) from my_all_objects where
regexp_like(object_name,'^A');
COUNT(*)
---------27648
Elapsed: 00:00:02.70
Mit LIKE:
SQL> select count(*) from my_all_objects where object_name like
'A%';
COUNT(*)
---------27648
Elapsed: 00:00:00.43
Fazit
Oracle hat reguläre Ausdrücke deutlich performanter und besser in SQL nutzbar als mit dem bisher verfügbaren PL/SQL Package implementiert. Dabei wurden auch Feinheiten wie der Globalisation Support nicht vergessen.
Nichtsdestotrotz sind (Kombinationen von) LIKE Abfragen immer noch sinnvoll, sie sind in der Regel schneller als ein regulärer Ausdruck. Letzteren wird
man also vor allem dort anwenden, wo bis jetzt PL/SQL oder OWA_PATTERN
zum Einsatz kam.
Man sollte bei allem Enthusiuasmus aber nicht vergessen, dass die Kompaktheit der regulären Ausdrücke leicht zu Unlesbarkeit führen kann. Es ist daher
wichtig, die einzelnen Komponenten jeweils zu dokumentieren.
Literatur
[1] Oracle Regular Expressions – Pocket Reference, J. Gennick & P. Linsley, ISBN
0-596-00601-2
[2] Regular Expression – Pocket Reference. T. Stubblebine, ISBN 0-596-00415-X
[3] www.lexikon-online.info: Erklärung der Begriffe Berechenbarkeitstheorie &
formale Sprachen
[4] Mastering Regular Expressions, Jeffrey Friedl, ISBN 1-56592-257-3
[5] www.regular-expressions.info: Tutorial über reguläre Ausdrücke
Kontaktadressen:
Dr. Martin Wunderli
Trivadis AG
Europastrasse 5
CH-8152 Glattbrugg
Telefon:
Fax:
E-Mail:
Internet:
+41(0)1-808 70 20
+41(0)1-808 70 21
[email protected]
http://www.trivadis.com
Database
17. Deutsche ORACLE-Anwenderkonferenz