Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:sql_regular_expression

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
prog:sql_regular_expression [2020/04/27 16:23] – [Quellen] gpipperrprog:sql_regular_expression [2022/11/08 17:19] (aktuell) – [RegEx und ein Outer Join mit der (+) Syntax] gpipperr
Zeile 1: Zeile 1:
 +====== 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]]
 +
 +<note important>Der NLS_SORT Parameter steuert das Verhalten der Pattern bzgl. Spracheinstellungen!</note>
 +
 +
 +
 +
 +
 +==== 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' **
 +
 +<code sql>
 +select regexp_substr('Der Kohl kostet 1.10 Euro beim gp@tester.de','.*') from dual;
 +</code>
 +
 +
 +^Pattern^Erklärung^Beispiel^findet^
 +|<code>.</code> |Ein beliebiges Zeichen ohne "NewLine"| <code>'.'</code>    |'D'   |
 +|<code>^</code>         |Anfang einer Zeile                   | <code>'^Der'</code> |'Der' |
 +|<code>$</code>         |Ende einer Zeile                     | <code>'de$'</code>  |'de'  |
 +|<code>.*</code>        |0,1 oder mehrere Zeichen             | <code>'.*'</code>   |alles |
 +|<code>.+</code>        |1 oder mehr Zeichen                  | <code>'.+'</code>   |alles |
 +|<code>.?</code>        |0 oder genau 1 Zeichen               | <code>'.?'</code>   |'D'   | 
 +|<code>.{n}</code>      |findet n Zeichen                     | <code>'.{3}'</code>  |'Der'   |
 +|<code>.{n,}</code>     |findet n Zeichen oder mehr           | <code>'.{3,}'</code> |alles  |   
 +|<code>.{n,m}</code> |findet n bis m Zeichen               | <code>'.{3,5}</code> |'Der K'|
 +|<code>[abc]</code> |Finde ein Zeichen in der Liste       | <code>'[kohl]'</code> |'o'    |
 +|<code>[a-z]</code>      |Finde ein Zeichen von A bis Z       | <code>'[a-z]'</code>  |'D'     | 
 +|<code>[[:alpha:]]</code>| Suche einen Buchstaben             | <code>'[[:alpha:]]'</code> |'D' |
 +|<code>[0-9]</code>      |Suche ein Zahl von 0 bis 9          | <code>'[0-9]'</code> |'1'        |
 +|<code>[[:punct:]]</code>|Suchen nach einem Punkt             | <code>'[[:punct:]]'</code> |'.'        |
 +|<code>[[:digit:]]</code>| Suche eine Zahl                    | <code>'[[:digit:]]'</code>|'1' |
 +|<code>[[:space:]]</code>| Suche nach einem Whitespace        | <code>'[[:space:]]'</code> |' ' |
 +|<code>[[=e=]]</code>    | Suche nach einer Klasse eines Zeichens |<code>'[[=e=]]'</code> |'e' |
 +
 + 
 +
 +==Suche genau ein Wort mit dem ersten Buchstaben K==
 +
 +^ Pattern ^ Erklärung ^ findet ^
 +|<code>'[k].* '</code> | 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 ^
 +|<code>'K[^[:space:]]+'</code> | 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 ^
 +|<code>'[[:digit:]]+[.][[:digit:]]{1,2}'</code> | 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 <key> [ [=e=] ] </key> 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. 
 +
 +<code sql>
 +select regexp_replace('+45 987 789.78989 nur nachmittags','[^[:digit:]]','') from dual
 +
 +4598778978989
 +
 +</code>
 +
 +
 +== Die letzten drei Stellen einer Zahl abschneiden==
 +
 +Suche Zahlen bis . dann Zahl, ab 1 bis 3 Zeichen noch belassen, rest weg:
 +<code sql>
 +select  regexp_substr ('10.3088888',  '[[:digit:]]+[.][[:digit:]]{1,3}' ) from dual;
 +
 +10.308
 +</code>
 +
 +
 +
 +----
 +
 +
 +== Anzahl der Zeichen in einem String ==
 +
 +Zählen alle ; in diesem String
 +<code sql>
 +select REGEXP_COUNT('1;2;3;4;5;6',';') from dual;
 +----
 +5
 +
 +</code>
 +
 +----
 +
 +
 +==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:
 +<code sql>
 +select  username
 +     , regexp_instr(username,'[^qwertzuiopasdfghjklyxcvbnm1234567890@.-_]')
 +     , data_pool
 +  from export_members   
 + where regexp_instr(replace(lower(username),'-',''),'[^qwertzuiopasdfghjklyxcvbnm1234567890@._]') > 0 
 +/
 +</code>
 +
 +
 +
 +Prüfe auf Umlaute in Tabellennamen einer Datenbank:
 +
 +<code sql>
 +select * 
 +  from dba_tables t 
 +where regexp_instr(t.table_name,'[^QWERTZUIOPASDFGHJKLYXCVBNM1234567890$_]') > 0 
 +/
 +</code>
 +
 +----
 +
 +
 +== Dopplete Worte in einem Text erkennen ==
 +
 +<code sql>
 +
 +select regexp_substr('AB CD DE DE FG'
 +                    ,'(^|[[:space:]]+)([[:alpha:]]+)[[:space:]]+\2($|[[:space:]]+)')  as example
 +  from dual
 +/  
 +
 +example
 +-------
 + DE DE
 +</code>
 +
 +
 +
 +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:
 +<code sql>
 +
 +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
 +
 +</code>
 +
 +
 +
 +
 +== 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
 +
 +<code sql>
 +
 +-- 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
 +
 +
 +</code>
 +
 +Beispiel: Doppelte Wort in einem String entfernen
 +
 +<code sql>
 +
 +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
 +
 +</code>
 +
 +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:
 +
 +<code sql>
 +--------------------------------------------
 +-- 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 ? !!
 +
 +</code>
 +
 +
 +
 +----
 +
 +==== 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:
 +
 +
 +<code sql>
 +# 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')
 +</code>
 +
 +Eine weitere Verbesserung der Laufzeit ließ sich durch das Setzen von NLS Sort auf binary erreichen
 +
 +<code sql>
 +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')
 +</code>
 +
 +=== 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:
 +<code sql>
 + 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
 +</code>  
 +
 +
 +
 +----
 +
 +=== Einen YYYMD Datumsstring zerlegen ===
 +
 +Funktioniert leider so nicht, da hier sich Doppeldeutigkeiten ergeben:
 +
 +<code sql>
 +
 +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
 +
 +</code> 
 +
 +So geht das dann leider nicht ..... hier nur als schlechtes Beispiel belassen ...
 +
 +
 +
 +----
 +
 +=== E-Mail Adresse validieren ===
 +
 +Funktion:
 +
 +<code plsql>
 +
 +-----------------------------------
 +--  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;
 +</code>
 +
 +----
 +
 +
 +=== 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:
 +
 +<code sql>
 +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     
 +
 +</code>
 +
 +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:
 +
 +<code sql>
 +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  
 +
 +</code>
 +
 +----
 +
 +==== 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