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.a 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:

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

 
1. Prüfung auf unerwünschte Zeichen mit REGEXP_LIKE

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

REGEXP_LIKE(Suchstring, Muster, Match-Parameter)

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):

SELECT name, plz, strasse FROM firma
WHERE NOT REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$');

NAME        PLZ   STRASSE
Maier       00000 24 a, Langstr.
meyer       0674  1a,Langstr.
Mayerhöfer  00345 10-11, Langstr.
Müller      -     235b, Langstr.
meir        12345 101, Langstr.
müller      12098 10 c, Langstr.
Hintermeier 11937 180, Holzweg
mAYR        19207 5d, Gärtnerplatz
MILLER      1525  10 a, Langstr.

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.

REGEXP_REPLACE(Suchstring, Muster, Ersatzstring, Position, Vorkommen, Match-Parameter)

• 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 man mittels REGEXP_REPLACE in einem Rutsch entfernen. (Wenn der Ersatzstring leer bleibt, kann man ihn auch weglassen):

UPDATE firma SET plz = REGEXP_REPLACE(plz, '[^0-9]','')
WHERE NOT REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$'); 
9 Zeilen wurden aktualisiert.

    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:

SELECT name, plz, strasse FROM firma
WHERE not REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$')OR plz IS NULL;

NAME       PLZ    STRASSE
Maier      00000  24 a, Langstr.
meyer      0674   1a,Langstr.
Mayerhöfer 00345  10-11, Langstr.
Müller            235b, Langstr.
MILLER     1525   10 a, Langstr.

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

UPDATE firma SET plz = ... WHERE name = ...

 
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:

ALTER TABLE firma
ADD CONSTRAINT firma_plz_check
CHECK (REGEXP_LIKE(plz, '^0[1-9][0-9]{3}$|^[1-9][0-9]{4}$'));

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

ALTER TABLE firma
ADD CONSTRAINT firma_mail_check
CHECK(REGEXP_LIKE(email, '^\w+\.\w+@\w+\.de$'));

    \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.:

^[-._0-9a-zA-Z]+@[-_0-9a-zA-Z]+\.[A-Za-z]{2,4}$'

 
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.

REGEXP_SUBSTR(Suchstring, Muster, Position, Vorkommen, Match-Parameter)

• 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:

SELECT INITCAP(REGEXP_SUBSTR(email, '[^.]+')||' '||name) AS Name FROM firma;

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:

SELECT LTRIM(REGEXP_REPLACE(strasse, '([^,]+),([^,]+)','\2 \1'))
AS strasse FROM firma;

Langstr. 24 a
Langstr. 1a
Langstr. 10-11
......

    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:

CREATE OR REPLACE VIEW adressen
AS SELECT
INITCAP(REGEXP_SUBSTR(email, '[^.]+')||' '||name) AS Name, plz,
LTRIM(REGEXP_REPLACE(strasse, '([^,]+),([^,]+)','\2 \1')) AS strasse
FROM firma;

SELECT * FROM adressen;
NAME             PLZ   STRASSE
Johann Maier     12345 Langstr. 24 a
Peter Meyer      12345 Langstr. 1a
Frank Mayerhöfer 12345 Langstr. 10-11
.......

 
6. Aufteilung von Strings in 2 Spalten mit REGEXP_SUBSTR

Die folgende View stellt alle Informationen in getrennten Spalten dar:

CREATE OR REPLACE VIEW adressen
AS SELECT INITCAP(REGEXP_SUBSTR(email, '[^.]+')) AS Vorname,
          INITCAP(name) as Nachname, plz,
          LTRIM(REGEXP_SUBSTR(strasse, '([^,]+)',1,2)) AS strasse,
          LTRIM(REGEXP_SUBSTR(strasse, '([^,]+)')) AS Nr
FROM firma;

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):

SELECT * FROM adressen;
VORNAME NAME       PLZ   STRASSE  NR
Johann  Maier      12345 Langstr. 24 a
Peter   Meyer      12345 Langstr. 1a
Frank   Mayerhöfer 12345 Langstr. 10-11
....

 
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:

SELECT name, strasse FROM firma
WHERE strasse LIKE '%Lang%'
ORDER BY LTRIM(strasse);

NAME       STRASSE
meyer      1a,Langstr.
MILLER     10 a, Langstr.
müller     10 c, Langstr.
meir       101, Langstr.
Mayerhöfer 10-11, Langstr.
....

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

SELECT INITCAP(name) Name, LTRIM(REGEXP_SUBSTR(strasse,'[^,]+')) Nr
FROM firma
WHERE strasse LIKE '%Lang%'
ORDER BY TO_NUMBER(REGEXP_SUBSTR(strasse, '[0-9]+')), LTRIM (strasse);

NAME       NR
Meyer      1a
Miller     10 a
Müller     10 c
Mayerhöfer 10-11
....

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:

SELECT INITCAP(name) Name,
       LTRIM(REGEXP_REPLACE(REGEXP_SUBSTR(strasse,'[^,]+'),'( ){2,}',
       ' ')) Nr
FROM firma
WHERE strasse LIKE '%Lang%'
ORDER BY TO_NUMBER(REGEXP_SUBSTR(strasse, '[0-9]+')), LTRIM (strasse);

NAME    NR
Meyer   1a
Miller  10 a
Müller  10 c
....

    '( ){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.:

UPDATE firma SET telefon = REGEXP_REPLACE(telefon, '[^0-9]');
14 Zeilen wurden aktualisiert

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

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

UPDATE firma SET telefon = '(+49-12)345-678-' || SUBSTR(telefon,-3);

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.

Hinweis; Oraclle unterstützt maximal 9 Gruppen (1-9)

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

ROLLBACK;
UPDATE firma SET telefon =
REGEXP_REPLACE(REGEXP_REPLACE(telefon,'[^0-9]'),
               '(0*49)?(0?12)?(\d{3})(\d{3})(\d{3})','(+49-12)\3-\4-\5');
SELECT telefon FROM FIRMA;
TELEFON
(+49-12)345-678-012
(+49-12)345-678-013
(+49-12)345-678-014
(+49-12)345-678-015

 
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:

SELECT name FROM firma WHERE REGEXP_LIKE (name, '^m[ae][yi].?r$','i');

    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.

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 ');