Inhaltsverzeichnis
Reguläre Ausdrücke (POSIX Standard) in der Oracle Datenbank in SQL verwenden
Ab der Version 10g stehen in der Oracle Datenbank reguläre Ausdrücke für die Suche in Texten in SQL zur Verfügung.
Die neuen Funktionen sind:
Einen Wert in einem String finden und extrahieren -regexp_substr
Bei der Suche mit regulären Ausdrücken geht es immer darum ein „Pattern“ mit einem String zu vergleichen.
Im Detail siehe: REGEXP_SUBSTR
REGEXP_SUBSTR(source_char, pattern[, position[, occurrence[, match_parameter ]]])
Parameter:
- source_string -Suchstring
- pattern - regex Pattern
- position - ab wo soll gesucht werden , default 1
- occurrence - der wievielte Treffer soll ausgeben werden, default 1
- match_parameter - steuert das Verhalten der RegEx Ausführung, wird zusammen gesetzt aus:
- i: to match case insensitively
- c: to match case sensitively
- n: to make the dot (.) match new lines as well
- m: to make ^ and $ match beginning and end of a line in a multiline string
Test String : 'Der Kohl kostet 1.10 Euro beim gp@tester.de'
SELECT regexp_substr('Der Kohl kostet 1.10 Euro beim gp@tester.de','.*') FROM dual;
Pattern | Erklärung | Beispiel | findet |
---|---|---|---|
. | Ein beliebiges Zeichen ohne „NewLine“ | '.' | 'D' |
^ | Anfang einer Zeile | '^Der' | 'Der' |
$ | Ende einer Zeile | 'de$' | 'de' |
.* | 0,1 oder mehrere Zeichen | '.*' | alles |
.+ | 1 oder mehr Zeichen | '.+' | alles |
.? | 0 oder genau 1 Zeichen | '.?' | 'D' |
.{n} | findet n Zeichen | '.{3}' | 'Der' |
.{n,} | findet n Zeichen oder mehr | '.{3,}' | alles |
.{n,m} | findet n bis m Zeichen | '.{3,5} | 'Der K' |
[abc] | Finde ein Zeichen in der Liste | '[kohl]' | 'o' |
[a-z] | Finde ein Zeichen von A bis Z | '[a-z]' | 'D' |
[[:alpha:]] | Suche einen Buchstaben | '[[:alpha:]]' | 'D' |
[0-9] | Suche ein Zahl von 0 bis 9 | '[0-9]' | '1' |
[[:punct:]] | Suchen nach einem Punkt | '[[:punct:]]' | '.' |
[[:digit:]] | Suche eine Zahl | '[[:digit:]]' | '1' |
[[:space:]] | Suche nach einem Whitespace | '[[:space:]]' | ' ' |
[[=e=]] | Suche nach einer Klasse eines Zeichens | '[[=e=]]' | 'e' |
Suche genau ein Wort mit dem ersten Buchstaben K
Pattern | Erklärung | findet |
---|---|---|
'[k].* ' | Suche in kleines K gefolgt von beliebigen Zeichen und dann ein Leerzeichen | 'kostet 1.10 Euro beim' |
Hinweis: da ein Leerzeichen ein beliebiges Zeichen ist wird bis zum letzten Leerzeichen gesucht! Stichwort „greediness“
Besser:
Pattern | Erklärung | findet |
---|---|---|
'K[^[:space:]]+' | Suche Worte mit K , K gefolgt von KEINEM Leerzeichen aber mindestens einen Zeichen und gebe das ganze Wort aus | 'Kohl' |
Suche den Preis in unserem Test String
Pattern | Erklärung | findet |
---|---|---|
'[[:digit:]]+[.][[:digit:]]{1,2}' | Suche eine Zahl (eine oder mehr) gefolgt von einem Punkt und mit einer Zahl mit zwei Stellen | '1.10' |
Buchstaben Klassen erkennen
Je nach eingestellten NLS_SORT Paramegter kann mit einer equivilance Class [ [=e=] ] nach allen Arten von einem Buchstaben gesucht werden, wie einem „è“ oder „e“.
siehe auch : http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch5lingsort.htm#NLSPG305
Hier wird erwähnt dass die UTF8 Zeichen wie die Deutschen Umlaute hier nicht unterstützt werden.
In Telefonnummer nur die Zahlen ausgeben
Suche alle NICHT Zahlen und ersetze diese durch nichts um nur die Zahlenwerte der Telefon Nr. zu erhalten.
SELECT regexp_replace('+45 987 789.78989 nur nachmittags','[^[:digit:]]','') FROM dual 4598778978989
Die letzten drei Stellen einer Zahl abschneiden
Suche Zahlen bis . dann Zahl, ab 1 bis 3 Zeichen noch belassen, rest weg:
SELECT regexp_substr ('10.3088888', '[[:digit:]]+[.][[:digit:]]{1,3}' ) FROM dual; 10.308
Anzahl der Zeichen in einem String
Zählen alle ; in diesem String
SELECT REGEXP_COUNT('1;2;3;4;5;6',';') FROM dual; ---- 5
Prüfen ob ungültige Zeichen zum Beispiel in einer E-Mail Adresse enthalten sind
Suchen nach Zeichen die NICHT in der Suchmenge sind!
Suche nach E-Mail Adressen die ungültige Zeichen enthalten könnten:
SELECT username , regexp_instr(username,'[^qwertzuiopasdfghjklyxcvbnm1234567890@.-_]') , data_pool FROM export_members WHERE regexp_instr(REPLACE(LOWER(username),'-',''),'[^qwertzuiopasdfghjklyxcvbnm1234567890@._]') > 0 /
Prüfe auf Umlaute in Tabellennamen einer Datenbank:
SELECT * FROM dba_tables t WHERE regexp_instr(t.table_name,'[^QWERTZUIOPASDFGHJKLYXCVBNM1234567890$_]') > 0 /
Dopplete Worte in einem Text erkennen
SELECT regexp_substr('AB CD DE DE FG' ,'(^|[[:space:]]+)([[:alpha:]]+)[[:space:]]+\2($|[[:space:]]+)') AS example FROM dual / example ------- DE DE
Wie funktioniert das ganze?
Einen Wert in einem String ersetzen - REGEXP_REPLACE
Mehr siehe hier:REGEXP_REPLACE
REGEXP_REPLACE(source_string, pattern[, replace_string [, position[,occurrence, [match_parameter]]]])
Ersetze den Preis in unserem Test String
Beispiel mit dem RegEx Pattern vom obigen substr Beispiel:
SELECT regexp_replace('Der Kohl kostet 1.10 Euro beim gp@tester.de' ,'[[:digit:]]+[.][[:digit:]]+' ,'20.30') AS test_string FROM dual; TEST_STRING -------------------------------------------- Der Kohl kostet 20.30 Euro beim gp@tester.de
Arbeiten mit Backrefenzen
Mit Backrefenzen können Ergebnisse eines Pattern Match „eingefangen“ werden. Alle Pattern Ausdrücken in einer Klammer „( .. ) “ stehen können der Reihe nach mit \1 … \9 referenziert werden (nur 9 Backreferenzen möglich).
Beispiel: Drehen zweier Strings, wie Nachname / Vorname
-- getrennt durch , SELECT regexp_replace('Gunther,Pippèrr','(.*),(.*)','\2 \1') AS example FROM dual; example --------------- Pippèrr Gunther -- getrennt durch einen oder mehreren Spaces SELECT regexp_replace('Gunther Pippèrr','(.*)[[:space:]]+(.*)','\2 \1') AS example FROM dual; example --------------- Pippèrr Gunther
Beispiel: Doppelte Wort in einem String entfernen
SELECT regexp_replace('AB AB CD DE DE AZ FG FG' ,'(^|[[:space:]]+)([[:alpha:]]+)[[:space:]]+\2($|[[:space:]]+)' ,'\1\2\3') FROM dual / Example -------------- AB CD DE AZ FG
Problem: obiger Ausdruck funktioniert nicht bei folgender Folge „AA AA BB BB CC CC“ ⇒ „AA BB BB CC“ - hmmm…..
Debuggen eines regulären Ausdruck
Die einzelnen Matches auf den Test String anzeigen:
-------------------------------------------- -- alle treffer nach einander ausgeben lassen -- DECLARE -- test string v_value varchar2(100):= 'AA AA BB BB CC CC DD'; v_regex varchar2(100):= '(^|[[:space:]]+)([[:alpha:]]+)([[:space:]]+)(\2+)($|[[:space:]]+)'; BEGIN -- anzahl möglicher treffer mit regexp_count ermitteln FOR i IN 1..regexp_count(v_value,v_regex) loop dbms_output.put_line('run ::'||to_char(i,'09')||' - *' ||regexp_substr(v_value,v_regex,1,i)||'*'); END loop; END; / run :: 01 - *AA AA * run :: 02 - * CC CC * -- ab hier gab es dann keinen Match mehr !! Hier ist der Fehler! BB BB wird nicht erkannt !! !! Aber warum ? !!
Performance Überlegungen
Auf einer großen Datenmenge ist ein regulärere Ausdruck meist immer deutlich langsamer als ein normaler Like Operator.
Wird ein überpropertionaler CPU Bedarf in den Ausführungsplänen sichtbar, kann es sich auch recht oft um einen Bug handeln.
So sind die beiden folgenden Ausdrücke im Prinzip gleich, laufen aber auf 20E06 Datensätze komplett anderes:
# Schlecht - Laufzeit über 1,25 h SELECT COUNT(1) FROM addresses WHERE REGEXP_LIKE(mail_adress, '^(\S)*@(\S*\.)*google\.com$', 'i') # Gut - Laufzeit ca 5 Minunte SELECT COUNT(1) FROM addresses WHERE REGEXP_LIKE(mail_adress,'^.+[@].*google[[:punct:]]com$', 'i')
Eine weitere Verbesserung der Laufzeit ließ sich durch das Setzen von NLS Sort auf binary erreichen
ALTER SESSION SET nls_sort=BINARY; # Gut - Laufzeit ca 3 Minunte, weniger CPU Bedarf SELECT COUNT(1) FROM addresses WHERE REGEXP_LIKE(mail_adress,'^.+[@].*google[[:punct:]]com$', 'i')
Function based index
Auch für ein REGEXP kann ein „Function Based“ Index ( Siehe Creating a Function-Based Index hilfreich sein.
RegEx und ein Outer Join mit der (+) Syntax
Auch mit regexp_like kann mit der (+) Syntax für das Abbilden eines Outer Joins gearbeitet werden:
WITH mother AS ( SELECT 1 id FROM dual UNION ALL SELECT 2 FROM dual ) , child AS ( SELECT 1 id, 'Tom' name FROM dual UNION ALL SELECT 2 , 'John' FROM dual ) SELECT m.id , c.name FROM mother m , child c WHERE m.id = c.id(+) AND regexp_like(c.name (+), '^[T-z]+') / ID NAME ------------ ---- 1 Tom 2
Einen YYYMD Datumsstring zerlegen
Funktioniert leider so nicht, da hier sich Doppeldeutigkeiten ergeben:
SELECT regexp_substr(DMONAT,'^(201[[:digit:]])') AS YEAR , CASE LENGTH(regexp_replace(DMONAT,regexp_substr(DMONAT,'^(201[[:digit:]])'),'')) WHEN 4 THEN regexp_replace(DMONAT,regexp_substr(DMONAT,'^(201[[:digit:]])'),'') WHEN 3 THEN regexp_replace(regexp_replace(DMONAT,regexp_substr(DMONAT,'^(201[[:digit:]])'),''),'([[:digit:]])([[:digit:]][[:digit:]])','0\1\2') WHEN 2 THEN regexp_replace(regexp_replace(DMONAT,regexp_substr(DMONAT,'^(201[[:digit:]])'),''),'([[:digit:]])([[:digit:]])','0\10\2') END AS month_date , DMONAT , regexp_replace(DMONAT,regexp_substr(DMONAT,'^(201[[:digit:]])'),'') MONTHDAY FROM GPI_TABLE GROUP BY DMONAT / YEAR MONTH_DATE DMONAT MONTHDAY --------------- --------------- --------------- --------------- 2014 0129 2014129 129 2014 0812 2014812 812
So geht das dann leider nicht ….. hier nur als schlechtes Beispiel belassen …
E-Mail Adresse validieren
Funktion:
----------------------------------- -- validateEMail -- Prüfe ob die Email gültig ist -- Email normaliseren und dann prüfen ----------------------------------- FUNCTION validateEMail( p_email VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS v_return VARCHAR2(512); v_email VARCHAR2(512); v_ident_string VARCHAR2(128):='^([a-zA-Z0-9_\.\-]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})$'; v_count PLS_INTEGER:=0; BEGIN -- normalisiere v_email:=LTRIM(RTRIM(p_email)); v_email:=REPLACE(v_email,',',''); v_email:=REPLACE(v_email,';',''); v_email:=REPLACE(v_email,' ',''); v_email:=LOWER(v_email); -------- -- prüfe v_count:=REGEXP_COUNT(v_email, v_ident_string); IF v_count > 0 THEN v_return:=v_email; ELSE -- falls nicht gültig verwerfe die E-mail Adresse v_return:=NULL; END IF; RETURN v_return; END validateEMail;
String in Elemente zerlegen
In einer Log Tabelle sind in einer Spalte bestimmte Informationen in einer Liste aufgeführt, die mit | getrennt ist.
Wie: „ Column1:ValueA | Column2:ValueB | Column3:ValueC | Column4:ValueD | Column4:ValueE | “
Ziel ist es den Wert von Column3 zu extrahieren, wir brauchen also alles jeweils zwischen den „|“ und dann den dritten Match:
WITH DATA AS ( SELECT 'Column1:ValueA | Column2:ValueB | Column3:ValueC | Column4:ValueD | Column4:ValueE |' AS log_val FROM dual ) SELECT ltrim( TRIM( regexp_substr( regexp_substr( a.log_val,'[^|]+',1,3) ,'[:].+ ',1,1 ) ),':') AS Column3 FROM DATA a; Column3 -------- ValueC
Der Trick dahinter ist es den String zu zerlegen mit Matches für eine Zeichenkette OHNE den „|“ und den „3“ Match herauszufiltern, diesen Teilstring dann bei „:“ zu trennen und dann mit Trimmen dafür sorgen das Leerzeichen und das „:“ verschwinden.
Alle in einer Spalte darstellen:
WITH DATA AS ( SELECT 'Column1:ValueA | Column2:ValueB | Column3:ValueC | Column4:ValueD | Column5:ValueE |' AS log_val FROM dual ) SELECT TRIM( regexp_substr(a.log_val, '[^|]+', 1, level) ) AS columValues FROM DATA a CONNECT BY level<regexp_count(a.log_val,'[^|]+')+1; columValues ---------- Column1:ValueA Column2:ValueB Column3:ValueC Column4:ValueD Column5:ValueE