====== 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