Reguläre Ausdrücke in Oracle 10 g - · PDF fileOracle 10g: Reguläre...

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

Dr. Martin WunderliTrivadis AG

www.trivadis.com

Oracle 10g: Reguläre Ausdrücke 2 © 2004

Reguläre Ausdrücke in Oracle 10g

> Grundlagen

> Vor Oracle 10g

> Ab Oracle 10g

> Check/Suchen/Ersetzen

> Ein komplexeres Beispiel

> Performance

> Fazit

Know-how. Wir wissen wie.

> Agenda


Wer suchet, der findet

> Um in einer gegebenen Menge von Datensätzen zu suchen, gibt esverschiedene Methoden

> Den Weg der Suche beschreiben, d.h. prozedural. Beispielsweise in derForm von Schleifen und IF-Anweisungen, in der Form von Navigationsanweisungen bei Netzwerkdatenbanken usw.

> Das Ergebnis der Suche beschreiben, d.h. deklarativ. Unter diese Rubrikfallen 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 vieleZeichen (%) 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 eineRegular Expression/einen regulären Ausdruck


Theoretischer Hintergrund

> Reguläre Ausdrücke sind bekannt aus derBerechenbarkeitstheorie, einem Teilgebiet der theoretischenInformatik

> Reguläre Ausdrücke bilden eine Famile von kompakten formalenSprachen. Mit einer solchen Sprache lassen sich Mengen von Zeichenketten beschreiben

> Beispiel: Unter der Annahme, dass der Punkt . für ein beliebigesZeichen steht, definiert der reguläre Ausdruck a.b die Mengeder Wörter (=Sprache) {aac,abc,acc} über dem Alphabet {a,b,c}. ac oder axc wären nicht Teil dieser Sprache


Wieso reguläre Ausdrücke?

> Die Mächtigkeit der regulären Ausdrücke liegt darin, dass man auf kleinem Raum mit wenigen, speziellen Symbolen sehrkomplexe 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 derVerwendung von regulären Ausdrücken immer den Aufbau in Einzelschritten dokumentieren


Die wichtigsten Symbole in regulären Ausdrücken

> Sollen Sonderzeichen wie ( und [ nicht zur Bildung von Symbolen verwendetwerden, muss man sie explizit mit \ markieren

Verankern die Regular Expression am Beginn/Ende einer Zeile/des zudurchsuchenden Strings

^/$

Entweder die Zeichenkette xx oder die Zeichenkette yyxx|yy

Referenzierung einer Gruppierung, hier der zweiten\2

Gruppierung, welche z.B. wiederholt oder ersetzt werden kann( )

Zeichenklasse, hier alle alphanummerischen Zeichen[:alnum:]

Genau ein Zeichen aus der Menge 0-9 und A-F[0-9A-F]

Genau 1 beliebiges Zeichen.

3 bis 6-malige Wiederholung des vorangehenden Ausdrucks{3,6}

Genau 4-malige Wiederholung des vorangehenden Ausdrucks{4}

0 oder 1-malige Wiederholung des vorangehenden Ausdrucks?

0/1 bis n-malige Wiederholung des vorangehenden Ausdrucks*/+

BedeutungSymbol


Einfache Beispiele für reguläre Ausdrücke

> Auf den Ausdruck bericht passt genau eine Zeichenkette, nämlichbericht

> Auf den Ausdruck bericht.. passt bericht01, bericht0a, bericht15 aber nicht bericht1

> Auf den Ausdruck bericht[0-8] passt bericht8 aber nichtbericht9

> Auf den Ausdruck bericht[[:digit:]]\.(txt|doc) passtbericht1.txt und bericht2.doc, nicht aber bericht3-doc

> Auf den Ausdruck bericht[A-Z]\.doc passt berichtA.doc aberauch gegenberichtA.doc und berichtB.document

> Auf den Ausdruck ^bericht[A-Z]\.doc$ passen gegenberichtA.docund berichtB.document aber nicht


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



> Grundlagen

> Vor Oracle 10g

> Ab Oracle 10g



> Performance

> Fazit


> Agenda


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 nochvorhanden ist

> Es ist aber relativ langsam, eher für PL/SQL geeignet (die meistenFunktionen arbeiten mit out Parametern) und es implementiertreguläre Ausdrücke nicht vollständig

> In SQL verwendbar ist z.B. die Funktion AMATCH, die ab eineranzugegebenden Position in einem String zu Suchen beginnt

select count(*) from my_all_objectswhere owa_pattern.amatch(object_name,1,'A') > 0 ;

COUNT(*)----------

16256


DAS OWA_PATTERN Package (1)

> Die Performance ist aber wie erwähnt nicht umwerfend

> Wenn man aber ein Problem mit RegExp lösen muss, dann ist eszumindest eine Lösungsmöglichkeit

select count(*) from my_all_objectswhere owa_pattern.amatch(object_name,1,'A') >0 ;

COUNT(*)----------

16256

Elapsed: 00:00:15.66

select count(*) from my_all_objectswhere regexp_like(object_name,'^A') ;

COUNT(*)----------

16256

Elapsed: 00:00:00.58



> Grundlagen

> Vor Oracle 10g

> Ab Oracle 10g



> Performance

> Fazit


> Agenda


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


NLS Unterstützung

> Die NLS Unterstützung ist sehr umfassend

> '.' matched immer auf ein Zeichen, egal aus wievielen Bytes esbesteht

> 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 odernicht

> Die Reguläre Expression wird bei Bedarf in das Character Set des Suchstrings konvertiert



> Grundlagen

> Vor Oracle 10g

> Ab Oracle 10g



> Performance Aspekte

> Fazit


> Agenda


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 NULLCONSTRAINT check_part# CHECK (regexp_like(partno,'[A-F][[:digit:]]{2}\.[[:digit:]]{3}\.[[:digit:]]{3}'))

)


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 address2 FROM email3 WHERE regexp_like (address, '.*@.*\.(com|de)');

ADDRESS------------------------------Sven.Vetter@[email protected]@firma.de


Ersetzen mit REGEXP_REPLACE (1)

von [email protected] [email protected]@trivadis.com [email protected] ?

> In Oracle 10g sehr einfach mit Back References:SELECT address,

regexp_replace (address, '(.*)\.(.*)@(.*)', '\2.\1@\3')FROM email

SELECT substr(address,instr (address, '.') + 1,instr (address, '@') - instr (address, '.') - 1) || '.'

|| substr (address, 1, instr (address, '.') - 1)|| substr (address, instr (address, '@'))

FROM email


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_neu6 FROM email;

ADDRESS ADRESS_NEU------------------------------ [email protected] [email protected]@trivadis.com [email protected]@firma.com [email protected]


Ersetzen: Auf der Wunschliste…

> Das wäre schön…

> Aus 'Meine Telefonnummer ist 987 65 43' würde durch

eine korrekte Telefonnummer: +4119876543

update phoneset nr_text = '+411\1' -- funktioniert nicht!where regexp_like(nr_text, '([[:digit:]]{7})')



> Grundlagen

> Vor Oracle 10g

> Ab Oracle 10g




> Fazit


> Agenda


Telefonnummern-Check und Formatierung (1)

> Gegeben sei folgende Tabelle mit Telefonnummern von Kunden:

> Wir wollen nun zum einen nur die korrekten ZürcherTelefonnummern daraus selektieren und zum andern dieseformatiert in internationaler Schreibweise ausgeben

+41 / 52 987 65467

+41 / (0)1 987 65456

01 / 987 65 445

+41 1 987 65 434

01.98.76.523

01-98'76'5412

01/987-65401

Telefonnummer


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:

> Demo…

+41198765455

+41198765444

+41198765433

+41198765412

+41198765401

Telefonnummer



> Grundlagen

> Vor Oracle 10g

> Ab Oracle 10g




> Fazit


> Agenda


Bemerkungen zur Performance

> Reguläre Ausdrücke benutzen keine Indizes, jedes regexp_likeetc. 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. Dadurchkö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



> Grundlagen

> Vor Oracle 10g

> Ab Oracle 10g



> Performance

> Fazit


> Agenda


Regular Expressions: Kernaussagen…

Im Kern geht es um Daten.

> der Trivadis

> 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 Aussenstehen-der 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

Reguläre Ausdrücke in Oracle 10 g - · PDF fileOracle 10g: Reguläre...

Documents

Transcript of Reguläre Ausdrücke in Oracle 10 g - · PDF fileOracle 10g: Reguläre...