Cornel Brücher 28. Mai 2019

Oracle: Regular Expressions

Ein regulärer Ausdruck beschreibt mit Literalen und Platzhaltern die Eigenschaften einer gesuchten Zeichenkette. © Adobe: vectorfusionart / stock.adobe.com / 202583749 — © Adobe: vectorfusionart

Mit der Einführung von SQL-Funktionen für reguläre Ausdrücke beendete Oracle das dunkle Zeitalter der Textmustersuche, in welchem der Datenbank mit LIKE nur eine nebulöse Vorstellung gesuchter Zeichenketten vermittelt werden konnte.

Es war schon immer eine Strafarbeit, Zeichenketten oder ganze Textfiles nach irgendwelchen Mustern zu durchsuchen, wenn keine regulären Ausdrücke zur Verfügung stehen. Wohl dem, der sich auf (U|Li)n[i|u]x tummelt, und mit (s)ed, vi, awk und Perl verwöhnt wird.

Wie suchte man damals mit SQL nach einer Zeichenkette, deren genaue Schreibweise man nicht kennt? Nehmen wir beispielsweise den Namen Meier mit seinen vielen Ausprägungen. Ohne reguläre Ausdrücke könnte man alle bekannten Schreibweisen mit OR verknüpfen:

SELECT * FROM meierei
WHERE name = 'Maier'
OR    name = 'Meier'
OR    name = 'Mayr'
...

Natürlich wird man mindestens eine Schreibweise übersehen, die dann im Ergebnis fehlt. Man könnte auch mit LIKE und der reichhaltigen Auswahl von zwei Metazeichen ("%"=beliebig viele Zeichen, "_" = ein Zeichen) diverse Suchmuster erstellen und verknüpfen:

SELECT * FROM meierei
WHERE name LIKE 'Me%r'
OR    name LIKE 'Ma_er'
...

Das ist jetzt zu unpräzise und liefert Ergebnisse, die man gar nicht haben will. Me%r könnte auch eine Metapher sein. Die Präzision eines regulären Ausdrucks kann nur durch intensiven Gebrauch der Funktion substr() zusammen mit AND und OR erreicht werden. Das musste viele Oracle-Jahre lang ausreichen.

Was lange währt...

Die Verwendung regulärer Ausdrücke zur Verarbeitung von Zeichenketten ist auf allen Unix- und ähnlichen Systemen seit den 70er Jahren etabliert, beginnend mit dem legendären ed. Aber erst der POSIX-Spezifikation der IEEE und der Open Group gelang es in den 90er Jahren, die unterschiedlichen Dialekte verschiedener Tools wieder einzufangen und eine einheitliche Grundlage zu schaffen. Eine Auswahl häufig genutzter Ausdrücke finden Sie in Tabelle 1. Oracle führte mit der Version 10g im Jahr 2003 die regulären Ausdrücke in SQL ein.

...wird endlich gut

Die Oracle-Datenbank stellt in SQL (und PL/SQL) fünf Funktionen für die Arbeit mit regulären Ausdrücken zur Verfügung. Diese können auf die Datentypen CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB und NCLOB angewendet werden. Damit können also nicht nur Zeichenketten, sondern auch ganze in (N)CLOBS abgelegte Texte mit regulären Ausdrücken durchsucht werden. Auch die Auswahl der in Oracle implementierten regulären Ausdrücke ist reichhaltig. Der POSIX-Standard IEEE Std 1003.1 ist mit Notation und Regeln für Basic und Extended Regular Expressions vollständig implementiert, und noch zusätzlich um mehrsprachliche Unterstützung erweitert. Perl-erfahrene Entwickler kommen auch auf ihre Kosten, da die gängigen Perl-Erweiterungen zu regulären Ausdrücken ebenfalls zur Verfügung stehen, solange sie dem POSIX-Standard nicht widersprechen.

Aufbau eines regulären Ausdrucks

Ein regulärer Ausdruck beschreibt mit Literalen und Platzhaltern die Eigenschaften einer gesuchten Zeichenkette. Ein normaler Buchstabe steht für genau diesen Buchstaben an dieser Stelle. Der einfache reguläre Ausdruck abc steht für diese drei Buchstaben in genau dieser Reihenfolge irgendwo in der durchsuchten Zeichenkette. Der wichtigste Unterschied zu LIKE ist hier, dass ein LIKE-Suchmuster sich immer auf das gesamte durchsuchte Textfeld bezieht, während ein regulärer Ausdruck nur an irgendeiner Stelle im Textfeld vorkommen muss, um ein Ergebnis zu liefern. Soll der Ausdruck dem gesamten Textfeld entsprechen, muss das spezifiziert werden.

Für eine Auswahl verschiedener Zeichen an einer Position werden diese Zeichen mit eckigen Klammern zusammengefasst. Der reguläre Ausdruck [abc]xyz bedeutet "a oder b oder c, gefolgt von xyz". Die POSIX-Zeichenklassen, erkennbar an der Klammerung aus eckigen Klammern und Doppelpunkten, beschreiben ebenfalls genau ein Zeichen.

In Tabelle 1 finden Sie eine beispielhafte Auswahl, mit der Sie auf der Datenbank mit den nachfolgend beschriebenen Funktionen sofort loslegen können.

Tabelle 1: Beispielhafte Suchmuster und Zeichenklassen

Ausdruck	Bedeutung (für ein Zeichen)
a	Buchstabe a an genau dieser Stelle
[abc]	a oder b oder c an dieser Stelle
[a-z]	Ein Kleinbuchstabe
[A-Z,a-z]	Ein Buchstabe
[0-9]	Eine Ziffer
.	Ein beliebiges Zeichen
[[:digit:]]	Die Ziffern 0-9, entspricht [0-9]
[[:upper:]]	Großbuchstaben (je nach Ländereinstellung mit Umlauten etc.), entspricht [A-Z]
[[:lower:]]	Kleinbuchstaben (je nach Ländereinstellung mit Umlauten etc.), entspricht [a-z]
[[:alpha:]]	Alle Groß- und Kleinbuchstaben, also [:upper:] und [:lower:] , entspricht [A-Z,a-z]
[[:alnum:]]	Alle Groß- und Kleinbuchstaben sowie alle Ziffern, also [:alpha:] und [:digit:] , entspricht [A-Z,a-z,0-9]
[[:punct:]]	Satzzeichen wie: . , " ' ? ! ; : # $ % & ( ) * + - / < > = @ [ ] \ ^ _ { } ~
[[:print:]]	Alle druckbaren Zeichen, also [:alnum:] , [:punct:] und SPACE
[[:graph:]]	Alle druckbaren Zeichen ohne SPACE, also [:alnum:] und [:punct:]
[[:blank:]]	SPACE und TAB
[[:space:]]	Alle whitespace Characters ( SPACE, TAB, VT, CR, NL, FF)
[[:cntrl:]]	Steuerzeichen ( NL CR LF TAB VT FF NUL SOH STX EXT EOT ENQ ACK SO SI DLE DC1 DC2 DC3 DC4 NAK SYN ETB CAN EM SUB ESC IS1 IS2 IS3 IS4 DEL)
[[:xdigit:]]	Hexadezimale Ziffern (0-9, A-F, a-f)
[[=a=]]	Äquivalenzklasse. Alle Buchstaben, deren Basis a ist, also â, á, ä etc.

Das reicht aber erst, um ein Suchmuster für ein einziges Zeichen zu definieren. Wenn mehrere Zeichen nacheinander dem beschriebenen Muster entsprechen sollen, können wir Wiederholungsfaktoren einsetzen (s. Tabelle 2).

Tabelle 2: Wiederholungsfaktoren werden nach Zeichen oder Zeichenklassen eingefügt

Ausdruck	Bedeutung
*	0- bis n-mal
?	0- bis 1-mal
+	1- bis n-mal
{n}	genau n-mal
{n,}	mindestens n-mal
{n,m}	n- bis m-mal

Beispiel: Der Ausdruck [0-9]{5} beschreibt eine Folge aus 5 Ziffern, die in einer Zeichenkette enthalten ist. Für eine Postleitzahl reicht das noch nicht, denn vor oder nach der Ziffernfolge könnte noch jede Menge Unsinn stehen. Um das auszuschließen, können wir den regulären Ausdruck am Anfang und am Ende der durchsuchten Zeichenkette verankern (s. Tabelle 3).

Tabelle 3: Verankern des regulären Ausdrucks

Anker	Bedeutung
^ (vor dem Ausdruck)	Beginn der untersuchten Zeichenkette
$ (nach dem Ausdruck)	Ende der untersuchten Zeichenkette

Der vollständige reguläre Ausdruck für eine Inlands-Postleitzahl ist ^[0-9]{5}$. Er beschreibt eine Zeichenkette, die von Anfang (^) bis Ende ($) aus 5 Ziffern besteht.

Regex-Funktionen

Oracle stellt für den Einsatz regulärer Ausdrücke die folgenden Funktionen zur Verfügung.

REGEXP_LIKE

Die Funktion liefert, wie das altbekannte LIKE, als Ergebnis TRUE oder FALSE zurück.

Ein

WHERE spalte LIKE '%qwertz%'

wird einfach zu

WHERE regexp_like(spalte, 'qwertz')

Die genaue Syntax ist:

REGEXP_LIKE( source
           , regexp
           , modes )

Source ist die Zeichenkette, die durchsucht werden soll, regexp enthält den regulären Ausdruck, modes ist optional und modifiziert die Interpretation des regulären Ausdrucks. Hierzu stehen 4 Optionen zur Verfügung, von denen bis zu 3 kombiniert werden können:

i: Case insensitive.
c: Case sensitive. Der Defaultwert für c und i, die sich naturgemäß gegenseitig ausschließen, ist im Datenbankparameter NLS_SORT festgelegt. Nur wenn dieser auf _CI endet, wird case insensitive gesucht. (z. B.: GERMAN_CI).
n: Punkt = beliebiges Zeichen inklusive Newline. Der Punkt in einem regulären Ausdruck steht für jedes Zeichen ohne Newline, wenn n nicht gesetzt wird.
m: ^ vor dem regulären Ausdruck = Zeilenanfang, $ nach dem Ausdruck = Zeilenende. ^ und $ verankern einen regulären Ausdruck normalerweise am Anfang bzw. am Ende der gesamten untersuchten Zeichenkette, auch wenn diese viele Zeilen enthält, wie z. B. in einem CLOB.

REGEXP_LIKE kann selbstverständlich auch in Check Constraints verwendet werden, um die Einhaltung eines bestimmten Feldformats zu erzwingen. Das folgende Beispiel überprüft die Einhaltung des Formats der Spalte phone_number in der Tabelle hr.employees:

ALTER TABLE hr.employees
ADD CONSTRAINT c_phone_number
CHECK (REGEXP_LIKE(phone_number, '[[:digit:]]{3}\.[[:digit:]]{3}\.[[:digit:]]{4}')) NOVALIDATE;

Und schon können wir die Telefonnummer nur noch im vorgegebenen Format in der Tabelle employees ablegen (Beispiel: 212.555.4240).

REGEXP_INSTR

Die Funktion REGEXP_INSTR liefert die Position innerhalb einer Zeichenkette, auf die der reguläre Ausdruck passt. Die genaue Syntax ist:

REGEXP_INSTR
   ( source
   , regexp
   , position
   , occurrence
   , return_option
   , modes )

Source ist die Zeichenkette, die durchsucht werden soll, regexp enthält den regulären Ausdruck, position kennzeichnet die Stelle in der Zeichenkette, an der die Suche starten soll, occurrence das relevante Auftreten des Musters (bei mehrfachen Treffern in der Zeichenkette). Mit return_option lässt sich einstellen, ob die Position des 1. Zeichens des Treffers (0), oder des 1. Zeichens nach dem Treffer zurückgeliefert werden soll (1). Der Parameter modes funktioniert bei alllen Regex-Funktionen wie bei REGEXP_LIKE beschrieben. Das folgende Beispiel liefert die Position einer gängigen E-Mail-Adresse in einer Zeichenkette:

SELECT REGEXP_INSTR('Email: MOS.C6502@quantentunnel.de','[[:alnum:],_\-\.]+@[[:alnum:]]+\.[[:alpha:]]{1,6}')  
FROM DUAL;

REGEXP_COUNT

Die Funktion REGEXP_COUNT liefert die Anzahl der Treffer, die mit einem regulären Ausdruck in einer Zeichenkette gefunden werden. Die genaue Syntax ist:

REGEXP_COUNT
   ( source
   , regexp
   , position
   , modes )

SELECT REGEXP_COUNT ('Abc123dEf456', '[0-9]') FROM dual

REGEXP_SUBSTR

Mit Hilfe dieser Funktion lässt sich der gesuchte Teil, der dem regulären Ausdruck entspricht, aus der durchsuchten Zeichenkette heraussuchen. Die genaue Syntax ist:

REGEXP_SUBSTR
   ( source
   , regexp
   , position
   , occurrence
   , modes )

Die Parameter entsprechen den gleichlautenden Parametern bei REGEXP_INSTR. Das folgende Beispiel liefert eine im Text enthaltene E-Mail-Adresse zurück:

SELECT REGEXP_SUBSTR ('Mail: MOS.C6502@quantentunnel.de Mobile: 0178/12345678',
'[A-Z,a-z,0-9]+[A-Z,a-z,0-9,\.]*@[A-Z,a-z,0-9]+\.[A-Z,a-z]{1,4}' )
FROM dual

REGEXP_REPLACE

Mit REGEXP_REPLACE können die dem regulären Ausdruck entsprechenden Textstellen gezielt ersetzt werden. Die genaue Syntax:

REGEXP_REPLACE
   ( source
   , regexp
   , replacement
   , position
   , occurrence
   , modes )

Der Parameter replacement kennzeichnet die Zeichenfolge, durch die der in source gefundene Teilstring ersetzt wird. Die anderen Parameter verhalten sich wie bei den anderen Funktionen. Zusätzlich können bis zu 9 Rückbezüge (Backreferences) auf den regulären Ausdruck verwendet werden. So kennzeichnet \1 den ersten gefundenen Ausdruck, \2 den Zweiten. Die Ersetzung findet nur statt, wenn der gesamte reguläre Ausdruck gefunden wird. Die gesuchten Teilstrings müssen im regulären Ausdruck mit runden Klammern gruppiert werden, um mittels Rückbezug adressiert zu werden.

SELECT regexp_replace('50321,Brühl','([0-9]*),([[:alpha:]]*)', '\1 ist die PLZ von \2')
FROM dual
Ergebnis: "50321 ist die PLZ von Brühl"

Praktischer Nutzen

Der größte Nutzen für die meisten Datenbankanwendungen dürfte in der Validierung von Eingabe- und Importdaten liegen. Durch die Formatprüfung vor oder während eines Imports oder einer Eingabe können Sie verhindern, dass Datenschrott es bis in die Verarbeitung schafft. Wer hat sich nicht schon mal so etwas banales wie eine Funktion gewünscht, die testet, ob ein VARCHAR2-Feld rein numerischen Inhalt hat?

ORA-01722: invalid number

Wenn Sie externe Daten in eine Oracle-Datenbank laden oder Nutzereingaben automatisch weiterprozessieren, dann ist der Kontakt mit dieser Fehlermeldung fast unvermeidlich. Das mag auch daran liegen, dass über eine Schnittstelle angelieferte Daten selten der vorangegangenen Beschreibung entsprechen. Die Übertragung eines numerisch erwarteten Inhalts einer Zeichenkette in ein echtes numerisches Feld ist die Stunde der Wahrheit. Auch ein Join mit einer NUMBER-Spalte auf der einen und einer hoffentlich numerischen VARCHAR2-Spalte auf der anderen Seite ist eine solche Sollbruchstelle. Eine Prüfung der "numerischen" Zeichenketten auf korrektes Zahlenformat ohne Verwendung regulärer Ausdrücke erfordert eine kreativ geschachtelte Konstruktion aus SQL-Funktionen wie translate(), replace() und instr(), um nur einige zu nennen. Mit regexp_like kein Problem mehr:

regexp_like(spaltenname,'^[0-9]+$')

Dieser Ausdruck lässt nur noch die rein numerischen Werte durch. Der reguläre Ausdruck beschreibt eine Zeichenkette, die von Anfang (^) bis Ende ($) aus 1-n (+) numerischen Zeichen (Menge [0-9]) besteht. (s. auch Tabelle 3). Die Verneinung der gesamten Bedingung mit einem vorangestellten NOT liefert genau die Einträge, die nicht nur aus Ziffern bestehen. Die Verneinung des regulären Ausdrucks selbst müsste eine Zeichenkette beschreiben, die wenigstens ein (+) nichtnumerisches Zeichen (Menge [^0-9]) enthält: [^0-9]+. Das ^innerhalb der Klammer verneint die angegebene Zeichenmenge.

Reguläre Ausdrücke sind auch dann sehr hilfreich, wenn Sie nach Zeichenketten suchen, die aus silben- oder lautorientierten Sprachen mit anderen Schriftsystemen stammen, und in unserem Alphabet nur ungenau abgebildet werden können. Um reguläre Ausdrücke leicht zu erstellen, formulieren Sie das gesuchte Muster zuerst als präzise sprachliche Beschreibung, wie oben bei der Postleitzahl: "Eine Zeichenkette, die von Anfang bis Ende aus 5 Ziffern besteht".

Alle gängigen Meiers von Meier bis Mayr finden Sie übrigens mit: