Regular Expressions in Oracle - Teil II

Die regulären Ausdrücke waren schon einmal Thema eines Monatstipps (April 2004), als sie mit der Oracle Version 10g als Neuerung erschienen waren. 

Diesmal soll es vor allem um einfache Anwendungsbeispiele für die Funktionen REGEXP_LIKE, REGEXP_SUBSTR und REGEXP_REPLACE gehen.

Wenn man sich mit der gewöhnungsbedürftigen Syntax (einen Überblick finden Sie am Schluss) erst einmal vertraut gemacht hat, können reguläre Ausdrücke beim Formatieren und Bereinigen von Tabelleninhalten, bei der Formulierung von komplizierten Check-Constraints u. ä. sehr gute Dienste leisten.

Ausgangspunkt für die Beispiele ist die folgende Tabelle (s. Anhang), die mit den regexp-Funktionen überprüft, bereinigt und ggf. vor falschen Eingaben geschützt werden sollte:

REGEXP_LIKE vergleicht den Suchstring mit einem Muster und gibt true oder false zurück.

Die Angabe des Match-Parameters (siehe Anhang) ist optional.

Fangen wir mit der PLZ-Spalte an, in der nur 5 Ziffern stehen sollen und nichts anderes (wenn die PLZ mit einer 0 anfängt, darf an der 2. Stelle keine stehen):

Erläuterungen:

• eckige Klammern schließen eine Auswahl von Zeichen ein, [0-9] steht für die Ziffern von 0 bis 9, (alternative Formulierungen sind \d (ab 10g2) oder [[:digit:]]).
  Die Zahlen in geschweiften Klammern geben die Anzahl der Wiederholungen an, [0-9]{3} steht für 3 Ziffern.
• ^ und $ stehen für Anfang und Ende eines Ausdrucks. ^0 passt auf alle Muster, die mit einer 0 anfangen,[0-9]{3}$ auf alle, die mit 3 Ziffern aufhören.
• Vorsicht: Das Caret-Zeichen ^ hat diese Bedeutung nur, wenn es außerhalb von eckigen Klammern steht.
• Die Postleitzahlen mit einer Null am Anfang passen auf das Muster
  ^0[1-9][0-9]{3}$ (hier darf an der 2. Stelle keine 0 vorkommen), alle anderen auf ^[1-9][0-9]{4}$.
• Die beiden alternativen Muster werden durch das Oder-Zeichen | getrennt.

2. Beseitigung von unerwünschten Zeichen mit REGEXP_REPLACE

Die Oracle-Funktion REGEXP_REPLACE ersetzt das Muster im Suchstring durch den Ersatzstring.

Position:         Position im Suchstring, an dem der Mustervergleich anfängt, default ist 1.

Vorkommen:  das nte Vorkommen des Musters im Suchstring, default ist 1.

Die Angabe von Position, Vorkommen und Match-Parameter ist optional.

Die störenden Leerzeichen und Buchstaben in der PLZ-Spalte kann mittels REGEXP_REPLACE in einem Rutsch entfernen. (Wenn der Ersatzstring leer bleibt, kann man ihn auch weglassen):

• Ein Caret-Zeichen innerhalb eines Klammerausdrucks kehrt die Zeichenauswahl um,
  [^0-9] steht also für alle Zeichen außer Ziffern, statt [^0-9] kann man auch \D oder [^[:digit:]] schreiben (s. Anhang).

Kontrolle:

Diese 5 Felder muss man von Hand korrigieren, bevor man einen Check-Constraint einrichten kann:

3. Verwendung von REGEXP_LIKE in Check-Constraints

Nach der Korrektur lässt sich durch Hinzufügen eines check-Constraints sicherstellen, dass in Zukunft keine Buchstaben, Sonder- oder Leerzeichen eingegeben werden:

Ähnlich funktioniert die Einrichtung eines Check-Constraints der e-mail-Adressen, z.B.:

• \w (ab 10g2)  steht für alle alphanumerischen Zeichen inklusive "_", + für eine beliebige Anzahl davon. Oracle akzeptiert \w nicht innerhalb der Zeichenlisten in eckigen Klammern.
• Der Punkt muss durch einen Backslash maskiert werden, weil er sonst als beliebiges Zeichen interpretiert würde. (Nur innerhalb von eckigen Klammern muss man die Metazeichen wie Punkt, Stern und Plus etc. nicht maskieren).
• Das @ steht für sich selbst.
• Statt de könnte man auch eine Auswahl angeben: [de|com|org] oder dem User mit \d{2,4} noch freiere Wahl lassen.

Wenn man auch Bindestriche oder ähnliches in den e-mail-Adressen zulassen will, muss man das Muster anders formulieren (dafür kann man Name und Vorname in einem Klammerausdruck unterbringen), z. B.:

4. Einfache Extraktion von Teilstrings mit REGEXP_SUBSTR

REGEXP_SUBSTR gibt einem dem Muster entsprechenden Teilstring des Suchstrings aus.

Was vor 10g mitunter komplizierte Kombinationen der Funktionen instr, SUBSTR und length erforderte, läßt sich mit REGEXP_SUBSTR in sehr kurzen Ausdrücken bewerkstelligen.

Position:         Position im Suchstring, an dem der Mustervergleich anfängt, default ist 1.

Vorkommen:  das nte Vorkommen des Musters im Suchstring, default ist 1.

Die Angabe von Position, Vorkommen und Match-Parameter ist wiederum optional.

Die Sekretärin möchte eine Adressenliste der Mitarbeiter als View erstellen, die folgende Informationen enthalten soll: Vorname, Nachname, PLZ, Straße und Hausnummer.

Die Vornamen kann man aus den e-mail-Adressen extrahieren und mit den Nachnamen verknüpfen:

Aus johann.maier@chaos.de wird somit Johann Maier u.s.w. ...

• [^.] steht für alle Zeichen außer dem Punkt (innerhalb der Klammer steht der Punkt für sich selbst), per default wird der erste Teilstring ausgegeben, der dem Muster entspricht (vorkommen = 1).
• Wollte man den Nachnamen aus der Adresse holen, so müsste man REGEXP_SUBSTR(email, '[^.@]+',1,2) schreiben (Position = 1, Vorkommen =2)
  REGEXP_SUBSTR(email, '[^.@]+',2) dagegen gibt den Vornamen ab dem 2. Zeichen aus (Position = 2, Vorkommen = default =1).
• Die Oracle-Funktion INITCAP sorgt für Großbuchstaben am Anfang und Kleinschreibung für den Rest.

5. Drehung am Komma mit REGEXP_REPLACE

Die umgedrehte Reihenfolge von Hausnummer und Strasse im amerikanischen Stil läßt sich durch mittels REGEXP_REPLACE umkehren:

• Das Komma teilt den String in 2 Hälften, die runden Klammern dienen der Gruppierung.
• [^,]+ steht für ein bis beliebig viele Zeichen mit Ausnahme des Kommas.
• Durch die negative Formulierung läuft man nicht Gefahr, eine Art von Zeichen zu vergessen, zudem kann man sie für beide Seiten verwenden.
  Positiv formuliert müsste man das Muster z.B. so schreiben: '([- 0-9a-z]+),([a-zA-Z. ]+)'
• \1 und \2 sind sogenannte Rückwärtsreferenzen (back references), die sich auf die Gruppierungen im Suchstring beziehen. Der Ausdruck '\2 \1' zieht aus dem Muster die Gruppen 1 und 2 heraus (ohne das Komma), dreht sie um und fügt zwischen beiden ein Leerzeichen ein.
• Die Oracle-Funktion LTRIM entfernt führende Leerzeichen.

Jetzt können wir die View erstellen:

6. Aufteilung von Strings in 2 Spalten mit REGEXP_SUBSTR

Die folgende View stellt alle Informationen in getrennten Spalten dar:

Aus einem String wie z.B. '24 a, Langstr.' extrahiert

REGEXP_SUBSTR(strasse, '([^,]+)',1,2))das 2. Vorkommen des Musters (Strasse), und REGEXP_SUBSTR(strasse, '([^,]+)',1,1)) das 1. Vorkommen (Hausnummer, die beiden Einser können hier auch wegfallen, weil sie der Default sind):

7. Sortierung von alphanumerischen Einträgen mit REGEXP_SUBSTR

Alle Angestellten, die in der Langstrasse wohnen, möchten eine Fahrgemeinschaft bilden. Der Fahrer hätte gern eine nach Hausnummern geordnete Liste der Namen. Wegen der Buchstaben und Leerzeichen etc. funktioniert der direkte Ansatz nicht, auch wenn man die vorangehenden Leerzeichen mit LTRIM eliminiert:

Eine Kombination dieser alphabetischen Sortierung mit der Sortierung nach dem Zahlenteilstring bringt dagegen das erwünschte Ergebnis:

REGEXP_SUBSTR(strasse, '[0-9]+') extrahiert die erste zusammenhängende Zahl aus dem String,(z.B. 10-11 ' 10) was die Umwandlung mit TO_NUMBER und damit die richtige Sortierung ermöglicht.

8. Entfernung von doppelten Leerzeichen mit REGEXP_REPLACE

Die Ausgabe kann man noch etwas verschönern, indem man die doppelten Leerzeichen in der Hausnummern-Spalte entfernt:

• '( ){2,}' findet Gruppen von mindestens 2 Leerzeichen und ersetzt sie durch eins: ' '. Die runden Klammern dienen nur der Übersichtlichkeit.

9. Formatierung von Ausdrücken mit REGEXP_REPLACE

Die Telefonnummern sollen von Leer-, Sonderzeichen u. ä. befreit werden und folgendes Format erhalten: (+49-12)345-6789-029. Die zu bereinigenden Einträge findet man mit:

SELECT telefon FROM firma WHERE REGEXP_LIKE(telefon, '[^-0-9]');

TELEFON
+49/012-345678-012
0049/012-345678-019
0049-12-345678-029
D012-345678-021
  012-345678-022
#012-345678023
?49-12-345678-024

[^-0-9] steht für alles außer Bindestrich und Ziffern. Mit REGEXP_REPLACE kann man so alle Zeichen außer den Ziffern entfernen, indem man den Ersatzstring einfach wegläßt.:

Für die Formatierung gibt es 2 Möglichkeiten:

a)   Verwendung der Oracle-SUBSTR-Funktion (weil sich nur die Durchwahl ändert):

SUBSTR(telefon,-3) startet beim dritten Zeichen von hinten und gibt den Rest aus.

b)   Verwendung von REGEXP_REPLACE, komplizierter, aber allgemeingültiger:

UPDATE firma SET telefon = REGEXP_REPLACE(telefon,
          '(0*49)?(0?12)?(\d{3})(\d{3})(\d{3})', '(+49-12)\3-\4-\5');
Gruppe:      1       2      3      4      5

Hier werden wieder Backreferences verwendet. Die Gruppen von Zeichen, die man umformatieren will, werden durch runde Klammern gekennzeichnet. 

Die Gruppen 1 (beliebig viele Nullen gefolgt von 49) und 2 (eine oder keine Null gefolgt von 12) die jeweils ein oder kein Mal vorkommen, werden durch (+49-12) ersetzt, die folgenden 3 Gruppen 3 - 5 mit Bindestrich aneinandergehängt.

Die Säuberung und Formatierung kann man mit einem geschachtelten REGEXP_REPLACE auch in einem Zug erledigen:

10. Suche nach Wörtern mit verschiedener Schreibweise über REGEXP_LIKE:

Angenommen, die Tabelle hätte 100000 Datensätze und wir wollen alle Angestellten suchen, die Maier oder so ähnlich heißen, unabhängig von Groß- und Kleinschreibung und Schreibweise:

• Hier wird nach einem m am Anfang, gefolgt von a oder e, gefolgt von y oder i gefolgt von 0-1 weiteren Buchstaben und einem r am Schluss gesucht.
• Der Parameter 'i' steht für case insensitive.

Eine Alternative dazu ist die Oracle-SOUNDEX-Funktion, die es schon vor 10g gab:

Zusammenfassung der wichtigsten Metazeichen und ihrer Bedeutung:

• Der Punkt steht für ein beliebiges Zeichen (ausser einen Zeilenumbruch). 
• eckige Klammern stehen für eine Auswahl von Zeichen,   [g-l] z. B. für g, h, i, j, k oder l
  [0-9A-G] für eine beliebige Ziffer oder einen Großbuchstaben von A bis G .
• Statt die Zeichenauswahl explizit anzugeben, kann man mit den sog. Charakterklassen arbeiten.
  Die wichtigsten sind:
  [:alnum:]   alle alphanumerischen Zeichen, [[:alnum:]] entspricht  also [0-9A-Za-z]
  \w                  alle alphanumerischen Zeichen und der Unterstrich "_"
  [:alpha:]   alle Buchstaben, [[:alpha:]] entspricht [A-Za-z]
  [:digit:] bzw. \d    alle Ziffern, [[:digit:]] entspricht [0-9]  
  [:space:] bzw.  \s    alle Zeichen, die man nicht sieht, Leerzeichen, Tabzeichen, Enter...
  \W, \D und S\   sind die Umkehrungen von  \w, \d und \s
  Die Abkürzungen \d, \w etc. stehen seit Version 10.2 zur Verfügung.
• Das pipe-Symbol | bedeutet "oder", z.B.  [ae|ä], [Otto|Emma], etc.
• Das Dollarzeichen $ verankert das vorausgehende Zeichen am Ende einer Zeile bzw. des Suchstrings,
  'r$' passt z.B. auf 'Müller' oder 'Herr', aber nicht zu 'rau' oder 'Karren'
• Das Caret-Zeichen ^ hat je nachdem, wo es steht, unterschiedliche Bedeutungen
  • ein Caret-Zeichen außerhalb eckiger Klammern  verankert das nachfolgende Zeichen am Beginn einer Zeile bzw. des Suchstrings
    ('^z.*' passt auf 'ziel', 'zeichen' etc, aber nicht auf 'platz', 'setzen')
  • ein Caret-Zeichen innerhalb eckiger Klammern schließt die nachfolgenden Zeichen aus.
    dementsprechend bedeutet:
    ^[:alnum:]] alle nicht-alphanumerischen Zeichen (Sonderzeichen, Leerzeichen...)
    [^0-9]                 alles ausser Ziffern
• Der Stern *   steht für keine oder beliebig viele Wiederholungen des vorausgehenden Elements (Zeichen oder Gruppe), '.*' passt also auf jeden String
• Das Fragezeichen ?  steht für keine oder eine Wiederholung
• Das Pluszeichen +   steht für eine oder beliebig viele Wiederholungen
• {n}   steht für n Wiederholungen
• {min,max}  steht für min bis max Wiederholungen des Elements ,
  {3,6} z. B für 3 bis 6,
  {3,}  für mindestens 3

Außerhalb von eckigen Klammern bzw. in Kombinationen mit Charakterklassen muss man den Metazeichen einen Backslash voranstellen, wenn man explizit nach ihnen sucht, 

z.B. \+, \?, \* etc.

Match-Parameter für die Oracle-REGEXP-Funktionen:

• i (case insensitive)
  Groß- und Kleinschreibung wird nicht berücksichtigt
• c (case sensitive)
  Groß- und Kleinschreibung wird berücksichtigt
• n (newline)
  Der Punkt kann in diesem Fall auch für einen Zeilenumbruch stehen.
• m (multiline)
  Die Zeichenkette wird als mehrzeilige Eingabe betrachtet.^und $ können dann auf jede Zeile angewandt werden und nicht nur für Anfang und Ende des Strings.

Mit dem folgenden SQL-Code können Sie sich die Übungstabelle selbst erstellen:

DROP TABLE firma;
CREATE TABLE firma(
name       VARCHAR2(20),
telefon    VARCHAR2(20),
email      VARCHAR2(30),
plz        VARCHAR2(10),
strasse    VARCHAR2(50));
INSERT INTO firma VALUES
('Maier','+49/012-345678-012','johann.maier@chaos.de','00000','24 a,Langstr.');
INSERT INTO firma VALUES
('meyer','012-345678-013','peter.meyer@chaos.de','a0674','1a,Langstr. ');
INSERT INTO firma VALUES'
('Mayerhöfer','012-345678-014','frank.mayerhoefer@chaos.de','00345','10-11,Langstr.');
INSERT INTO firma VALUES
('MEYR','012-345678-015','tomas.meyr@chaos.de','19387','16b,Waldgasse');
INSERT INTO firma VALUES
('Mayer','012-345678016','richard.mayer@chaos.de','11028',' 22,Richterstr.');
INSERT INTO firma VALUES
('Müller','012-345678-017','martin.mueller@chaos.de','-','235b,Langstr.');
INSERT INTO firma VALUES
('Mair','012-345678-018','wilhelmine.mair@chaos.de','12345',' 35a,Langstr.');
INSERT INTO firma VALUES
('meir','0049/012-345678-019','sabine.meir@chaos.de',' 12345','101,Langstr.');
INSERT INTO firma VALUES
('myers','0049-12-345678-029 ','jeff.myers@chaos.de','03927','10,Schlossallee');
INSERT INTO firma VALUES
('müller','D012-345678-021','kurt.müller2@chaos.de','12098 ','10 c,Langstr.');
INSERT INTO firma VALUES
('Hintermeier',' 012-345678-022','hansi.hintermeier@chaos.de',' 11937','180,Holzweg ');
INSERT INTO firma VALUES
('MEIER','#012-345678023','eva.meier@chaos.de','12346','301,Langstr.');
INSERT INTO firma VALUES
('mAYR','?49-12-345678-024','klaus.mayr@chaos.de',' 19207','5d,Gärtnerplatz ');
INSERT INTO firma VALUES
('MILLER','345678025','oscar.miller@chaos.de','1525','10 a,Langstr.');
COMMIT;