Regular Expressions

Regular Expressions
Autor: Martin Bracher, Trivadis GmbH
DOAGNews Q1_2004
Dieses Werk ist urheberrechtlich geschützt. Die dadurch begründeten Rechte, insbesondere die der
Übersetzung, des Nachdrucks, des Vortrags, der Entnahme von Abbildungen und Tabellen, der Funksendung, der Mikroverfilmung oder der Vervielfältigung auf anderen Wegen und der Speicherung in
Datenverarbeitungsanlagen,
bleiben,
bei
auch
nur
auszugsweiser
Verwertung,
vorbehalten.
Eine
Vervielfältigung dieses Werkes oder von Teilen dieses Werkes ist auch im Einzelfall nur in den Grenzen der
gesetzlichen Bestimmungen des Urheberrechtes der Bundesrepublik Deutschland vom 9. September 1965
in der jeweils geltenden Fassung zulässig. Sie ist grundsätzlich vergütungspflichtig. Zuwiderhandlungen
unterliegen den Strafbestimmungen des Urheberrechtsgesetzes.
©2004
Einleitung
Sie kennen sicher das Problem, Daten auf ihre formale Gültigkeit zu prüfen: Ist die E-MailAdresse richtig geschrieben? Hat die Kontonummer das richtige Format? Ist die eingegebene
IP-Adresse gültig? Ist die Telefonnummer plausibel?
Zu Zeiten von Oracle 8.0 hatte ich mal das Problem, in einer Tabelle, die in einem Textfeld
gespeicherten Telefonnummern auf Gültigkeit zu prüfen. Ich hätte mir damals gewünscht, es gäbe
sowas wie eine Regular-Expression-Funktion in Oracle. War damals leider nicht vorhanden und
mit like "%_" kommt man nicht sehr weit Aufwendige PL/SQL Programmierung war angesagt.
Regular Expression? Nie gehört? Dann versuche ich das mal ganz einfach zu erklären.
Sie kennen sicher die Möglichkeit, Dateinamen mit Platzhaltern zu suchen.
dir *.txt
liefert Ihnen alle Dateien, die mit „.txt“ enden. Oder
dir bericht??.txt
liefert Ihnen beispielsweise „bericht99.txt“ oder „berichtXY.txt“, nicht aber „bericht999.txt“. Unter Unix
können Sie mit
ls bericht[0-9][0-9].txt
noch einschränken, dass Sie an diesen zwei Stellen nur Ziffern wünschen.
Die Verwendung der Platzhalter „*“ und „?“ unter Windows und Unix oder „%“ und „_“ in SQL
ermöglicht es uns, nach einfachen Mustern zu suchen. Mit Regular Expressions, im folgenden
Regexp genannt, können wir ebenfalls nach Mustern suchen und zwar auch nach sehr komplexen
Mustern. Unser Beispiel von oben sieht dann so aus:
bericht..\.txt
Der Punkt steht für ein beliebiges Zeichen, deshalb muss ein Punkt durch „\.“ dargestellt werden.
Nun, eine Regexp ist natürlich viel mächtiger.
bericht([[:digit:]]{1,3})?\.(txt|doc)
Hier wird nach „bericht“ entweder keine oder eine „?“ 1- bis 3stellige „{1,3}“ Zahl erwartet,
danach folgt ein Punkt und die Endung „txt“ oder „doc“.
Tabellarisch zusammengefasst die häufigsten Regexp-Symbole:
©2004
Symbol
*
?
+
{4}
{3,6}
.
[0-9A-F]
[:alnum:]
()
\2
(xx|y)
^
$
Bedeutung
kein oder beliebig oft
kein oder einmal
ein oder beliebig oft
genau 4 Mal
zwischen 3 und 6 Mal
beliebiges Zeichen
ein Zeichen aus der Menge 0 bis 9 und A bis F
Zeichen-Klasse, hier Alphanumerische Zeichen (Buchstaben und Ziffern)
Gruppierung, wird als eigener Sub-Ausdruck behandelt
Referenzierung der 2. Gruppierung, d.h. zweites Klammerpaar ()
xx oder y
Anfang der Zeile
Ende der Zeile
Ab Oracle8i ist dann das owa_pattern Package hinzu gekommen, das aber so einige Nachteile hat:
• langsam
• mehr für PL/SQL geeignet (in/out Parameter bei den meisten Funktionen)
• nicht vollständige Implementation von Regexp
Nun habe ich mit Freuden gesehen, dass ab Oracle 10g Funktionen zum Umgang mit Regexp’s in
die SQL-Engine aufgenommen wurden:
• REGEXP_LIKE
• REGEXP_REPLACE
• REGEXP_INSTR
• REGEXP_SUBSTR
Interessant zu sehen ist, was sich mit den für Oracle 10g angekündigten regexp_* Funktionen
ändert und wie und wo wir sie vernünftig einsetzen können.
Aufgabe: Anzahl der Objekte, die mit einem 'A' beginnen
Diese Aufgabe lässt sich ganz einfach mit "like" lösen.
select count(*) from all_objects
where object_name like 'A%';
Nun versuchen wir es mit dem owa_pattern Package. Die Funktion "match" lässt sich in SQL
leider nicht verwenden, da sie in/out-Parameter verwendet. Es bleibt somit nur die "amatch"
Funktion, bei der man aber den Start angeben muss (hier 1. Stelle ein 'A')
select count(*) from all_objects
where owa_pattern.amatch(object_name,1,'A') >0 ;
Auf einer Oracle9i Datenbank auf meinem Laptop mit rund 6000 Objekten ist diese Variante
massiv langsamer. Während die 'like' Variante in 0.02 Sekunden fertig war, brauchte amatch
mehrere Sekunden!
©2004
Mit dem neuen REGEXP_LIKE sieht das dann so aus:
select count(*) from all_objects
where regexp_like(object_name,'^A') ;
Das ^ bedeutet, dass es sich um den Anfang handelt. Ein $ kennzeichnet das Ende.
Wie schnell ist nun diese Variante? Lassen Sie sich überraschen, sobald Sie im Besitz von Oracle
10g sind.
Aufgabe: Anzahl der Records, die irgendwo ein 'A' enthalten.
Auch diese Aufgabe lässt sich mit 'like' noch einfach lösen:
select count(*) from all_objects
where object_name like '%A%';
Nun versuchen wir es wieder mit dem owa_pattern Package. Bei der amatch-Funktion muss
wiederum der Start angegeben werden, somit kann nicht die genau gleiche Regexp gebraucht
werden wie beim regexp_like. Im Search-Pattern muss ".*" für beliebige Zeichen am Anfang
gesetzt werden, da das A an beliebiger Stelle stehen darf.
select count(*) from all_objects
where owa_pattern.amatch(object_name,1,'.*A') >0 ;
Mit regexp_like kann mit einer einfacheren Regular Expression gearbeitet werden:
select count(*) from all_objects
where regexp_like(object_name,'A') ;
Wollen Sie die Leistungsfähigkeit von regexp_like mit OWA_PATTERN vergleichen, dann
müssen Sie ehrlicherweise auch mit einer möglichst analogen Regexp testen
select count(*) from all_objects
where regexp_like(object_name,'^.*A') ;
Apropos Vergleich: Die Heimat der Regular Expressions ist die Unix Betriebssystem-Umgebung
mit ihren Tools wie z.B. egrep, sed oder perl. Interessant wird ein Vergleich von regexp_like mit
diesen Tools sein. Wenn nur bestimmte Daten von einem File in die Datenbank gelesen werden
sollen, besteht die Möglichket, diese bereits vor dem Einlesen mit grep zu filtern oder während
des Einlesens mit regexp_like zu filtern.
/u00/app/oracle> egrep "A" all_objects.lst > import_object.lst
/u00/app/oracle> egrep "^A" all_objects.lst > import_object.lst
©2004
Anwendung der Funktionen für Fortgeschrittene
Mit den beiden Beispielen wird man den Möglichkeiten der regexp_% Funktionen nicht gerecht.
Ich habe daher einige Versuche zum eingangs erwähnten Beispiel mit den Telefonnummern
gemacht, wie man mit diesen Funktionen gut arbeiten kann.
Die normalen Schweizer Telefonnummern bestehen prinzipiell aus einer 3stelligen Vorwahl 020
bis 099 oder 01 (Zürich), gefolgt von 7 Ziffern. Bei der Verwendung der internationalen Vorwahl
+41 wird die 0 der Vorwahl weggelassen.
Die Tabelle TELEFON enthält immer die gleiche Telefonnummer in unterschiedlichster
Schreibweise.
#
1
2
3
4
5
6
TFNR
01/987-6543
01-98'76'543
019.876.543
+41 1 987 65 43
01 / 987 65 43
+41 / (0)1 987 6543
Wie kann man nun prüfen, ob das eine gültige Zürcher Telefonnummer ist (Vorwahl 01), und sie
anschliessend einheitlich formatiert ausgeben?
Die Telefonnummer ist, wie es der Name schon sagt, eine Nummer, also eine Folge von Ziffern.
Menschen haben jedoch die Gewohnheit, die Ziffernfolge durch verschiedene Zeichen zu
gruppieren.
Um nicht alle Gruppierungsarten einzeln zu prüfen, wird es am Einfachsten sein, alle
Gruppierungszeichen zu entfernen. Dazu verwenden wir
regexp_replace(tfnr,'[^[:digit:]]','')
Wir ersetzen alles, das keine [^] Zahl [:digit:] ist, durch nichts ('', kann auch weggelassen werden).
Jetzt haben wir nur noch das "+" übersehen, das als Stellvertreter für die internationale Vorwahl
steht, für uns also eine Art Spezialziffer, die wir nicht verlieren dürfen. Wir fügen es deshalb noch
hinzu (mit \ geschützt, da + sonst eine spezielle Bedeutung hat):
regexp_replace(tfnr,'[^[:digit:]\+]')
select tfnr, regexp_replace(tfnr,'[^[:digit:]\+]') tfnr_clean
from telefon
#
1
2
3
4
5
TFNR
01/987-6543
01-98'76'543
019.876.543
+41 1 987 65 43
01 / 987 65 43
©2004
TFNR_CLEAN
019876543
019876543
019876543
+4119876543
019876543
6
+41 / (0)1 987 6543
+41019876543
Mit diesen bereinigten Nummern kann man nun viel einfacher die Gültigkeit prüfen. Die Nummer
ist formal gültig, wenn sie eine Vorwahl 01 oder +411 hat und danach 7 Stellen. Zu
berücksichtigen ist noch der ursprüngliche Spezialfall "+41 (0)1", den wir hier als Vorwahl +4101
betrachten (und bereinigen) können.
Die Vorwahl lässt sich durch folgende Regexp beschreiben:
'^(01|\+41(1|01))'
Die nachfolgenden Stellen beschreibt man mit:
'[[:digit:]]{7}$'
Wir möchten nun z.B. alle Telefonnummern einheitlich mit der internationalen '+411' Vorwahl
dargestellt haben. Dazu selektieren wir mit "where regexp_like" die passenden Telefonnummern
und formatieren sie dann mit regexp_replace nach unseren Vorstellungen um (wir ersetzen die
+4101 und 01 Vorwahl durch +411)
select
tfnr ,
regexp_replace(regexp_replace(tfnr,'[^[:digit:]\+]'), '^(01|\+4101)', '+411')
from
telefon
where
regexp_like(regexp_replace(tfnr,'[^[:digit:]\+]'),'^(01|\+41(1|01))[[:digit:]]{7}$')
#
1
2
3
4
5
6
TFNR
01/987-6543
01-98'76'543
019.876.543
+41 1 987 65 43
01 / 987 65 43
+41 / (0)1 987 6543
TFNR_INT
+4119876543
+4119876543
+4119876543
+4119876543
+4119876543
+4119876543
Es führen viele Wege nach Rom... Die Regexp in der where-Bedingung liesse sich z.B. auch so
formulieren:
'^(0|\+41[0]?)1[[:digit:]]{7}$'
Dies ist ein relativ einfaches Beispiel, das nur die "01"-Telefonnummern berücksichtigt. Mit
wenig Aufwand lassen sich aber auch alle anderen Vorwahlen wie z.B. "033" prüfen:
'^(0|\+41[0]?)(1|[2-9][[:digit:]])[[:digit:]]{7}$'
select
tfnr
, regexp_replace(regexp_replace(tfnr,'[^[:digit:]\+]'), '^(0|\+410)', '+41')
©2004
from telefon
where
regexp_like(regexp_replace(tfnr,'[^[:digit:]\+]'),
'^(0|\+41[0]?)(1|[2-9][[:digit:]])[[:digit:]]{7}$')
Fazit
•
•
•
•
•
Regular Expressions sind ein sehr mächtiges Werkzeug und es führen meist viele Wege zum
Ziel. Damit man den Überblick behält, sollte man nicht alle Bedingungen auf einmal zu
formulieren versuchen, sondern grob beginnen, testen und dann immer mehr verfeinern.
Sehr wichtig ist, dass man die einzelnen Teile der Expression genau dokumentiert. Ein
Aussenstehender hat sonst bei komplexeren Regexp’s kaum noch eine Chance zu verstehen,
was die Regexp eigentlich macht. Und nach kurzer Zeit weiss man es selbst wohl auch nicht
mehr.
Oracle stellt uns mit diesen Funktionen ein geniales Hilfsmittel zur Verfügung.
Laut Dokumentation verstehen die neuen Funktionen zur Zeit leider nur die Character
Classes (z.B. [[:alnum:]] ) und nicht auch die von grep und perl gewohnten Synonyme
(z.B. \d für [[:digit:]]). Dies wäre für die Lesbarkeit und das Schreiben von Vorteil.
Innerhalb der REGEXP_REPLACE Funktionen kann auf die durch Klammern gruppierten
Ausdrücke durch \n verwiesen werden, wobei n die n-te öffnende Klammer ist. Bei
regexp_replace(tfnr, '^01([[:digit:]]{7})', '+411\1')$
können die letzten 7 Ziffern durch \1 referenziert werden. Praktisch wäre noch, wenn die im
where gefundenen Klammer-Ausdrücke auf ähnliche Art bei einem Update zur Verfügung
stünden:
update telefon set tfnr='+411\3' --geht leider nicht
where regexp_like(regexp_replace(tfnr, '[^[:digit:]\+]'),
'^(01|\+41(1|01))([[:digit:]]{7})$')
•
•
•
Die letzten 7 Stellen wären hier in \3, weil es die 3. öffnende Klammer ist. Aber das geht
leider nicht, wir müssen auch im set-Teil mit regexp_replace arbeiten.
Die Funktionen sind ideal für formale Inhaltsprüfungen, welche über die Möglichkeiten von
LIKE hinausgehen. Sie ermöglichen es, viele Prüfungen, die bisher mühsam und ineffizient
mit PL/SQL gemacht werden mussten, durch eine effiziente, performante Standardfunktion
zu ersetzen.
Die Regexp Funktionen lassen sich auch in Constraints verwenden.
Wenn sich jedoch etwas mit LIKE lösen lässt, dann sollte man es auch weiterhin mit LIKE
lösen, da dies technisch weniger aufwendig und dadurch schneller ist.
Verwendete Dokumentation
http://otn.oracle.com/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
https://www.oracleworld2003.com/published/40105/40105_Gennick_04.ppt
https://www.oracleworld2003.com/published/40105/40105.doc
©2004
Viel Spaß mit Oracle und den Regular Expressions!
Martin Bracher
Trivadis GmbH
Max-Lang-Strasse 56
70771 Leinfelden-Echterdingen
[email protected]
Tel: +49 711 903 63 230
Fax: +49 711 903 63 259
©2004