Reguläre Ausdrücke in Oracle 10g

Reguläre Ausdrücke in Oracle 10g –
Waren Queries bis jetzt irregulär?
Dr. Martin Wunderli
Trivadis AG
www.trivadis.com
Reguläre Ausdrücke in Oracle 10g
>
Agenda
Know-how.
Wir wissen wie.
Oracle 10g: Reguläre Ausdrücke
>
Grundlagen
>
Vor Oracle 10g
>
Ab Oracle 10g
>
Check/Suchen/Ersetzen
>
Ein komplexeres Beispiel
>
Performance
>
Fazit
2
© 2004
Wer suchet, der findet
>
Um in einer gegebenen Menge von Datensätzen zu suchen, gibt es
verschiedene Methoden
>
Den Weg der Suche beschreiben, d.h. prozedural. Beispielsweise in der
Form von Schleifen und IF-Anweisungen, in der Form von
Navigationsanweisungen bei Netzwerkdatenbanken usw.
>
Das Ergebnis der Suche beschreiben, d.h. deklarativ. Unter diese Rubrik
fallen SQL Queries mit WHERE Bedingungen
» Diese WHERE Bedingungen waren bis jetzt beschränkt auf
Vergleichsoperatoren, u.a. auch LIKE. LIKE kann Platzhalter für beliebig viele
Zeichen (%) oder genau ein Zeichen (_) enthalten konnte
» Ab Oracle 10g kann die WHERE Bedingung auch die Definition eines Musters
enthalten, dem ein Datensatz genügen muss: Dieses Muster nennt man eine
Regular Expression/einen regulären Ausdruck
Oracle 10g: Reguläre Ausdrücke
3
© 2004
Theoretischer Hintergrund
>
Reguläre Ausdrücke sind bekannt aus der
Berechenbarkeitstheorie, einem Teilgebiet der theoretischen
Informatik
>
Reguläre Ausdrücke bilden eine Famile von kompakten formalen
Sprachen. Mit einer solchen Sprache lassen sich Mengen von
Zeichenketten beschreiben
>
Beispiel: Unter der Annahme, dass der Punkt . für ein beliebiges
Zeichen steht, definiert der reguläre Ausdruck a.b die Menge
der Wörter (=Sprache) {aac,abc,acc} über dem Alphabet
{a,b,c}. ac oder axc wären nicht Teil dieser Sprache
Oracle 10g: Reguläre Ausdrücke
4
© 2004
Wieso reguläre Ausdrücke?
>
Die Mächtigkeit der regulären Ausdrücke liegt darin, dass man
auf kleinem Raum mit wenigen, speziellen Symbolen sehr
komplexe Muster definieren kann
>
Komplexer als z.B. mit % und _, da die Prüfung von
Zeichenklassen, Anzahl der Wiederholung einer
(Teil-)Zeichenkette, Variationen etc. beschrieben werden können
>
Der Nachteil ist, dass die Komplexität leicht in
Unübersichtlichkeit ausarten kann. Man sollte also bei der
Verwendung von regulären Ausdrücken immer den Aufbau in
Einzelschritten dokumentieren
Oracle 10g: Reguläre Ausdrücke
5
© 2004
Die wichtigsten Symbole in regulären Ausdrücken
Symbol
Bedeutung
*/+
0/1 bis n-malige Wiederholung des vorangehenden Ausdrucks
?
0 oder 1-malige Wiederholung des vorangehenden Ausdrucks
{4}
Genau 4-malige Wiederholung des vorangehenden Ausdrucks
{3,6}
3 bis 6-malige Wiederholung des vorangehenden Ausdrucks
.
Genau 1 beliebiges Zeichen
[0-9A-F]
Genau ein Zeichen aus der Menge 0-9 und A-F
[:alnum:]
Zeichenklasse, hier alle alphanummerischen Zeichen
( )
Gruppierung, welche z.B. wiederholt oder ersetzt werden kann
\2
Referenzierung einer Gruppierung, hier der zweiten
xx|yy
Entweder die Zeichenkette xx oder die Zeichenkette yy
^/$
Verankern die Regular Expression am Beginn/Ende einer Zeile/des zu
durchsuchenden Strings
>
Sollen Sonderzeichen wie ( und [ nicht zur Bildung von Symbolen verwendet
werden, muss man sie explizit mit \ markieren
Oracle 10g: Reguläre Ausdrücke
6
© 2004
Einfache Beispiele für reguläre Ausdrücke
>
Auf den Ausdruck bericht passt genau eine Zeichenkette, nämlich
bericht
>
Auf den Ausdruck bericht.. passt bericht01, bericht0a,
bericht15 aber nicht bericht1
>
Auf den Ausdruck bericht[0-8] passt bericht8 aber nicht
bericht9
>
Auf den Ausdruck bericht[[:digit:]]\.(txt|doc) passt
bericht1.txt und bericht2.doc, nicht aber bericht3-doc
>
Auf den Ausdruck bericht[A-Z]\.doc passt berichtA.doc aber
auch gegenberichtA.doc und berichtB.document
>
Auf den Ausdruck ^bericht[A-Z]\.doc$ passen gegenberichtA.doc
und berichtB.document aber nicht
Oracle 10g: Reguläre Ausdrücke
7
© 2004
Literatur
>
http://www.trivadis.com: Dieser Vortrag und ein Artikel von
Martin Bracher zum Thema 'Regular Expressions'
>
http://www.regular-expressions.info: Eine Einführung in
Reguläre Ausdrücke
>
Mastering Regular Expressions, Jeffrey E.F. Friedl, O'Reilly
>
Regular Expressions Pocket Reference, Tony Stubblebine, O'Reilly
>
Oracle Regular Expressions Pocket Reference, Jonathan Gennick
& Peter Linsey, O'Reilly
Oracle 10g: Reguläre Ausdrücke
8
© 2004
Reguläre Ausdrücke in Oracle 10g
>
Agenda
Know-how.
Wir wissen wie.
Oracle 10g: Reguläre Ausdrücke
>
Grundlagen
>
Vor Oracle 10g
>
Ab Oracle 10g
>
Check/Suchen/Ersetzen
>
Ein komplexeres Beispiel
>
Performance
>
Fazit
9
© 2004
Das OWA_PATTERN Package (1)
>
Seit Oracle8i gibt es das OWA_PATTERN Package (pubpat.sql in
$ORACLE_HOME/rdbms/admin), welches auch in Oracle 10g noch
vorhanden ist
>
Es ist aber relativ langsam, eher für PL/SQL geeignet (die meisten
Funktionen arbeiten mit out Parametern) und es implementiert
reguläre Ausdrücke nicht vollständig
>
In SQL verwendbar ist z.B. die Funktion AMATCH, die ab einer
anzugegebenden Position in einem String zu Suchen beginnt
select count(*) from my_all_objects
where owa_pattern.amatch(object_name,1,'A') > 0 ;
COUNT(*)
---------16256
Oracle 10g: Reguläre Ausdrücke
10
© 2004
DAS OWA_PATTERN Package (1)
>
Die Performance ist aber wie erwähnt nicht umwerfend
select count(*) from my_all_objects
where owa_pattern.amatch(object_name,1,'A') >0 ;
COUNT(*)
---------16256
Elapsed: 00:00:15.66
select count(*) from my_all_objects
where regexp_like(object_name,'^A') ;
COUNT(*)
---------16256
Elapsed: 00:00:00.58
>
Wenn man aber ein Problem mit RegExp lösen muss, dann ist es
zumindest eine Lösungsmöglichkeit
Oracle 10g: Reguläre Ausdrücke
11
© 2004
Reguläre Ausdrücke in Oracle 10g
>
Agenda
Know-how.
Wir wissen wie.
Oracle 10g: Reguläre Ausdrücke
>
Grundlagen
>
Vor Oracle 10g
>
Ab Oracle 10g
>
Check/Suchen/Ersetzen
>
Ein komplexeres Beispiel
>
Performance
>
Fazit
12
© 2004
Reguläre Ausdrücke ab Oracle 10g
>
Oracle unterstüzt reguläre Expressions über 4 Funktionen, die in SQL und
PL/SQL verwendet werden können (Extended Regular Expressions nach IEEE
1003.1)
>
Als Input String sind alle Character Datentypen erlaubt ausser LONG
>
REGEXP_LIKE (string, pattern [, param]): BOOLEAN
» Pattern darf maximal 512 Bytes gross sein
>
REGEXP_INSTR (string, pattern [, pos
[, occurrence [, ret_opt [, param]]]]): Number
>
REGEXP_SUBSTR ((string, pattern [, pos
[, occurrence [, param]]]]): String
>
REGEXP_REPLACE ((string, pattern [, repl_string [, pos
[, occurrence [, param]]]]): String
>
Parameter: i=case insensitive, c=case sensitive, n=. matched Newline, m=^/$
matchen Anfang/Ende von Zeilen, nicht des Strings
Oracle 10g: Reguläre Ausdrücke
13
© 2004
NLS Unterstützung
>
Die NLS Unterstützung ist sehr umfassend
>
'.' matched immer auf ein Zeichen, egal aus wievielen Bytes es
besteht
>
Zeichenklassen passen sich an den verwendeten Zeichensatz an,
so enthält [:digit:] eventuell zusätzlich Arabisch-Indische Ziffern
٠, ١, ٢ bis ٩ usw.
>
[[=i=]] matched i, ï, í usw.
>
NLS_SORT Setting beeinflusst ob zwei Zeichen gleich sind oder
nicht
>
Die Reguläre Expression wird bei Bedarf in das Character Set des
Suchstrings konvertiert
Oracle 10g: Reguläre Ausdrücke
14
© 2004
Reguläre Ausdrücke in Oracle 10g
>
Agenda
Know-how.
Wir wissen wie.
Oracle 10g: Reguläre Ausdrücke
>
Grundlagen
>
Vor Oracle 10g
>
Ab Oracle 10g
>
Check/Suchen/Ersetzen
>
Ein komplexeres Beispiel
>
Performance Aspekte
>
Fazit
15
© 2004
Check Constraint mit REGEXP_LIKE
>
Eignet sich ausgezeichnet als Check-Condition auf Tabellen
>
Beispiel: Validierung einer Artikel-Nummer
» Format x99.999.999
» x = Grossbuchstabe zwischen A und F
» 9 = Zahl
CREATE TABLE parts
(partno VARCHAR2(11) NOT NULL
CONSTRAINT check_part# CHECK (regexp_like(partno,
'[A-F][[:digit:]]{2}\.[[:digit:]]{3}\.[[:digit:]]{3}'))
)
Oracle 10g: Reguläre Ausdrücke
16
© 2004
Suchen mit REGEXP_LIKE
>
Test in der WHERE Bedingung, ob regulärer Ausdruck
eingehalten wird
>
z.B. ob E-Mail-Adressen @ enthalten und mit .com oder .de
enden
SQL> SELECT address
2 FROM email
3 WHERE regexp_like (address, '.*@.*\.(com|de)');
ADDRESS
[email protected]
[email protected]
[email protected]
Oracle 10g: Reguläre Ausdrücke
17
© 2004
Ersetzen mit REGEXP_REPLACE (1)
von
[email protected]
[email protected]
nach
[email protected]
[email protected]
SELECT substr(address,
instr (address, '.') + 1,
instr (address, '@') - instr (address, '.') - 1
) || '.'
|| substr (address, 1, instr (address, '.') - 1)
|| substr (address, instr (address, '@'))
FROM email
>
In Oracle 10g sehr einfach mit Back References:
SELECT address,
regexp_replace (address, '(.*)\.(.*)@(.*)', '\2.\1@\3')
FROM email
Oracle 10g: Reguläre Ausdrücke
18
© 2004
?
Ersetzen mit REGEXP_REPLACE (2)
>
Ein etwas komplexerer Ausdruck
» Es ist nicht sicher, ob zwischen Name und Vorname wirklich ein "."
vorkommt, dieser soll aber nach der Reformatierung vorhanden sein
SQL> SELECT address,
2
regexp_replace (address,
3
'([[:alnum:]]+)\.?([[:upper:]])(.*)@(.*)',
4
'\2\3.\1@\4'
5
) adress_neu
6
FROM email;
ADDRESS
[email protected]
[email protected]
[email protected]
Oracle 10g: Reguläre Ausdrücke
19
ADRESS_NEU
[email protected]
[email protected]
[email protected]
© 2004
Ersetzen: Auf der Wunschliste…
>
Das wäre schön…
>
Aus 'Meine Telefonnummer ist 987 65 43' würde durch
update phone
set nr_text = '+411\1' -- funktioniert nicht!
where regexp_like(nr_text, '([[:digit:]]{7})')
eine korrekte Telefonnummer: +4119876543
Oracle 10g: Reguläre Ausdrücke
20
© 2004
Reguläre Ausdrücke in Oracle 10g
>
Agenda
Know-how.
Wir wissen wie.
Oracle 10g: Reguläre Ausdrücke
>
Grundlagen
>
Vor Oracle 10g
>
Ab Oracle 10g
>
Check/Suchen/Ersetzen
>
Ein komplexeres Beispiel
>
Performance Aspekte
>
Fazit
21
© 2004
Telefonnummern-Check und Formatierung (1)
>
Gegeben sei folgende Tabelle mit Telefonnummern von Kunden:
Telefonnummer
>
1
01/987-6540
2
01-98'76'541
3
01.98.76.52
4
+41 1 987 65 43
5
01 / 987 65 44
6
+41 / (0)1 987 6545
7
+41 / 52 987 6546
Wir wollen nun zum einen nur die korrekten Zürcher
Telefonnummern daraus selektieren und zum andern diese
formatiert in internationaler Schreibweise ausgeben
Oracle 10g: Reguläre Ausdrücke
22
© 2004
Telefonnummern-Check und Formatierung (2)
>
>
Was ist eine Zürcher Telefonnummer? Sie hat die Vorwahl 01 und
hat 7 Ziffern
Das Ziel ist also diese Ausgabe:
Telefonnummer
>
1
+4119876540
2
+4119876541
3
+4119876543
4
+4119876544
5
+4119876545
Demo…
Oracle 10g: Reguläre Ausdrücke
23
© 2004
Reguläre Ausdrücke in Oracle 10g
>
Agenda
Know-how.
Wir wissen wie.
Oracle 10g: Reguläre Ausdrücke
>
Grundlagen
>
Vor Oracle 10g
>
Ab Oracle 10g
>
Check/Suchen/Ersetzen
>
Ein komplexeres Beispiel
>
Performance Aspekte
>
Fazit
24
© 2004
Bemerkungen zur Performance
>
Reguläre Ausdrücke benutzen keine Indizes, jedes regexp_like
etc. führt also normalerweise einen Full Table Scan durch
>
Sollte ein regulärer Ausdruck häufig benutzt werden, kann man
aber darauf einen Function Based Index (FBI) anlegen. Dadurch
können regexp Abfragen sehr performant werden
>
LIKE ist in der Regel schneller:
select count(*) from my_all_objects where
regexp_like(object_name,'^A') ;
...
Elapsed: 00:00:00.58
select count(*) from my_all_objects where object_name like
'A%';
...
Elapsed: 00:00:00.02
Oracle 10g: Reguläre Ausdrücke
25
© 2004
Reguläre Ausdrücke in Oracle 10g
>
Agenda
Know-how.
Wir wissen wie.
Oracle 10g: Reguläre Ausdrücke
>
Grundlagen
>
Vor Oracle 10g
>
Ab Oracle 10g
>
Check/Suchen/Ersetzen
>
Ein komplexeres Beispiel
>
Performance
>
Fazit
26
© 2004
Regular Expressions: Kernaussagen…
>
>
Reguläre Ausdrücke sind extrem
mächtig und ermöglichen Lösungen,
die sonst sehr viel komplizierter
und/oder in PL/SQL implementiert
werden müssten
>
Dokumentation ist das A und O.
Ansonsten versteht ein Aussenstehender den Ausdruck nicht (und nach ein
paar Wochen auch der Autor nicht
mehr ☺)
>
Dort wo man schnell mit LIKE zum
Ziel kommt, sollte man weiterhin LIKE
benutzen: Es ist ist performanter
der Trivadis
Im Kern geht
es um Daten.
Oracle 10g: Reguläre Ausdrücke
27
© 2004