====== 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: * [[http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions007.htm#SQLRF52121|regexp_like - pürfen ob der Ausdruck enthalten ist]] * [[http://docs.oracle.com/cd/E18283_01/olap.112/e17122/dml_functions_2067.htm#CHDCGGJA|regexp_instr - Position ermitteln]] * [[http://docs.oracle.com/cd/E18283_01/olap.112/e17122/dml_functions_2069.htm#CHDBCDHG|regexp_substr - Substring ausgeben]] * [[http://docs.oracle.com/cd/E18283_01/olap.112/e17122/dml_functions_2068.htm|regexp_replace - Ersetzen im String]] * [[https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/REGEXP_COUNT.html|regexp_count - Anzahl der Treffer]] Der NLS_SORT Parameter steuert das Verhalten der Pattern bzgl. Spracheinstellungen! ==== 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: [[http://docs.oracle.com/cd/E18283_01/olap.112/e17122/dml_functions_2069.htm|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? {{:prog:regulaer_ausdruck_v01.png?700| Übersicht über den regulären Ausdruck zum finden doppelter Daten}} ---- ==== Einen Wert in einem String ersetzen - REGEXP_REPLACE ==== Mehr siehe hier:[[http://docs.oracle.com/cd/E18283_01/olap.112/e17122/dml_functions_2068.htm|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 [[http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes.htm#ADMIN11730|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 ---- ==== Quellen ==== Beispiele => https://ihateregex.io/ Oracle: * http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions129.htm * http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_regexp.htm#ADFNS232 Standard Regular Expressions patterns: * http://www.javascriptkit.com/javatutors/redev2.shtml VI * http://vimregex.com/ Online: * https://www.regextester.com/1911