Entfernen von nicht numerischen Zeichen aus einem VARCHAR2

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_REPLACEUm 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.

Leave a Reply

Your email address will not be published.