Table of Contents
Ziel
In diesem Blogeintrag wird gezeigt, wie man unerwünschte Zeichen aus einem VARCHAR2 Feld entfernt.
Testdaten
Als Beispiel wird eine Ortstabelle verwendet, die die Felder PLZ und Ort enthält. Wir nehmen an, dass die Applikation so desigint wurde, dass man in das PLZ Feld alphanumerische Zeichen einfügen kann.
CREATE TABLE t1 ( plz VARCHAR2(10), ort VARCHAR2(50) ) INSERT ALL INTO t1 VALUES ('41179', 'Mönchengladbach') INTO t1 VALUES ('D-40235', 'Düsseldorf') INTO t1 VALUES ('60313 (D)', 'Frankfurt am Main') INTO t1 VALUES('26233', 'Oldemburg') SELECT * FROM dual;
Die Beispieldaten sehen wie folgt aus.
PLZ ORT ---------- -------------------------------------------------- 41179 Mönchengladbach D-40235 Düsseldorf 60313 (D) Frankfurt am Main 26233 Oldemburg
Wie man sieht sind die PLZs in keinem einheitlichen Fortmat. Dies soll nun geändert werden.
Lösung
Zur Behebung dieses Problem bietet Oracle mehrere Funktionen an. Es besteht auch die Möglichkeit eine eigene Funktion hier für zu entwicklen, falls die von Oracle angebotenen Mittel nicht ausreichen sollten.
Möglichkeit 1: REGEXP_REPLACE
Die einfachste Möglichkeit unerwünschte Zeichen aus einem VARCHAR2 zu entfernen, ist die Funktion REGEXP_REPLACE. Um alle nicht numerischen Zeichen zu entfernen, muss die Funktion wie folgt aufgerufen werden.
REGEXP_REPLACE([Spalte],'[^0-9]') oder: REGEXP_REPLACE([Spalte],'[^[:digit:]]')
Daraus ergibt sich folgendes Ergebnis:
SELECT REGEXP_REPLACE(plz, '[^[:digit:]]') plz, ort FROM t1; PLZ ORT ---------- -------------------------------------------------- 41179 Mönchengladbach 40235 Düsseldorf 60313 Frankfurt am Main 26233 Oldemburg
Möglichkeit 2: TRANSLATE
Die zweite Funktion die Oracle hierfür anbietet ist TRANSLATE. Diese Funktion bekommt insgesamt 3 Parameter. Der 2. Parameter enthält die Zeichen nach denen gesucht werden soll. Der 3. Parameter enthält die “Übersetzungen” der Zeichen. Das heißt befindet sich zum Beispiel an der 5. Stelle im Suchparameter der Buchstabe “a” wird dieser mit dem Zeichen an der Position 5 im Übersetzungsparameter ausgetauscht. Gibt es an der 5. Stelle kein Zeichen mehr, wird das Zeichen aus dem Ergebnis entfernt.
TRANSLATE( [Spalte],'0123456789abcdefghijklmnopqrstuvwxyzäöüABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ ~!@#$%^&*()_+=-`{}[]|\?><,./"''','0123456789')
Wie man sieht muss man jedes Zeichen, das ersetzt bzw. in diesem Fall entfernt werden soll im Suchparameter angeben.
Hinweis: Der 3. Parameter darf keinen leeren String ” enthalten. Findet Oracle an dieser Stelle einen leeren String, wird immer null von der Funktion zurückgegeben. Damit dies nicht passiert muss es ein übereinstimmendes Zeichen geben. Ich habe an dieser Stelle zur Vereinfachung einfach alle Zahlen übernommen – es würde aber auch eine Zahl ausreichen.
Das Ergebnis sieht wie folgt aus:
SELECT TRANSLATE(plz,'0123456789abcdefghijklmnopqrstuvwxyzäöüABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ ~!@#$%^&*()_+=-`{}[]|\?<>,./"''','0123456789') plz, ort FROM t1; PLZ ORT ---------- -------------------------------------------------- 41179 Mönchengladbach 40235 Düsseldorf 60313 Frankfurt am Main 26233 Oldemburg
Zusammenfassung
Für das Ersetzen von bestimmten Zeichen bietet Oracle mehrere Möglichkeiten an. Wenn man sich gut mit regulären Ausdrücken auskennt, empfiehlt sich die Funktion REGEXP_REPLACE. Ist man auf diesem Gebiet nicht so bewandert, kann TRANSLATE vewendet werden. TRANSLATE erfordert man unter Umständen eine weitaus höhrere Tipparbeit.