prog:sql_regular_expression
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungNächste ÜberarbeitungBeide Seiten der Revision | ||
prog:sql_regular_expression [2020/04/27 15:49] – [Performance Überlegungen] gpipperr | prog:sql_regular_expression [2022/11/08 17:05] – [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:// | ||
+ | * [[http:// | ||
+ | * [[http:// | ||
+ | * [[http:// | ||
+ | * [[https:// | ||
+ | |||
+ | <note important> | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ==== Einen Wert in einem String finden und extrahieren -regexp_substr | ||
+ | |||
+ | Bei der Suche mit regulären Ausdrücken geht es immer darum ein " | ||
+ | |||
+ | Im Detail siehe: | ||
+ | |||
+ | |||
+ | **REGEXP_SUBSTR(source_char, | ||
+ | |||
+ | 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, | ||
+ | * 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(' | ||
+ | </ | ||
+ | |||
+ | |||
+ | ^Pattern^Erklärung^Beispiel^findet^ | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |< | ||
+ | |||
+ | |||
+ | |||
+ | ==Suche genau ein Wort mit dem ersten Buchstaben K== | ||
+ | |||
+ | ^ Pattern ^ Erklärung ^ findet ^ | ||
+ | |< | ||
+ | |||
+ | |||
+ | Hinweis: da ein Leerzeichen ein beliebiges Zeichen ist wird bis zum letzten Leerzeichen gesucht! Stichwort " | ||
+ | |||
+ | Besser: | ||
+ | ^ Pattern ^ Erklärung ^ findet ^ | ||
+ | |< | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | == Suche den Preis in unserem Test String == | ||
+ | |||
+ | ^ Pattern ^ Erklärung ^ findet ^ | ||
+ | |< | ||
+ | |||
+ | |||
+ | == Buchstaben Klassen erkennen == | ||
+ | |||
+ | Je nach eingestellten NLS_SORT Paramegter kann mit einer equivilance Class <key> [ [=e=] ] </ | ||
+ | |||
+ | siehe auch : http:// | ||
+ | |||
+ | 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(' | ||
+ | |||
+ | 4598778978989 | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | == Die letzten drei Stellen einer Zahl abschneiden== | ||
+ | |||
+ | Suche Zahlen bis . dann Zahl, ab 1 bis 3 Zeichen noch belassen, rest weg: | ||
+ | <code sql> | ||
+ | select | ||
+ | |||
+ | 10.308 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | == Anzahl der Zeichen in einem String == | ||
+ | |||
+ | Zählen alle ; in diesem String | ||
+ | <code sql> | ||
+ | select REGEXP_COUNT(' | ||
+ | ---- | ||
+ | 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: | ||
+ | <code sql> | ||
+ | select | ||
+ | , regexp_instr(username,' | ||
+ | , data_pool | ||
+ | from export_members | ||
+ | where regexp_instr(replace(lower(username),' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | Prüfe auf Umlaute in Tabellennamen einer Datenbank: | ||
+ | |||
+ | <code sql> | ||
+ | select * | ||
+ | from dba_tables t | ||
+ | where regexp_instr(t.table_name,' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | == Dopplete Worte in einem Text erkennen == | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select regexp_substr(' | ||
+ | ,' | ||
+ | from dual | ||
+ | / | ||
+ | |||
+ | example | ||
+ | ------- | ||
+ | DE DE | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | Wie funktioniert das ganze? | ||
+ | |||
+ | |||
+ | {{: | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ==== Einen Wert in einem String ersetzen - REGEXP_REPLACE ==== | ||
+ | |||
+ | |||
+ | Mehr siehe hier: | ||
+ | |||
+ | |||
+ | **REGEXP_REPLACE(source_string, | ||
+ | |||
+ | |||
+ | |||
+ | == Ersetze den Preis in unserem Test String | ||
+ | |||
+ | Beispiel mit dem RegEx Pattern vom obigen substr Beispiel: | ||
+ | <code sql> | ||
+ | |||
+ | select regexp_replace(' | ||
+ | ,' | ||
+ | ,' | ||
+ | |||
+ | TEST_STRING | ||
+ | -------------------------------------------- | ||
+ | Der Kohl kostet 20.30 Euro beim gp@tester.de | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | == Arbeiten mit Backrefenzen == | ||
+ | |||
+ | Mit Backrefenzen können Ergebnisse eines Pattern Match " | ||
+ | |||
+ | Beispiel: Drehen zweier Strings, wie Nachname / Vorname | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | -- getrennt durch , | ||
+ | |||
+ | select regexp_replace(' | ||
+ | |||
+ | example | ||
+ | --------------- | ||
+ | Pippèrr Gunther | ||
+ | |||
+ | -- getrennt durch einen oder mehreren Spaces | ||
+ | |||
+ | select regexp_replace(' | ||
+ | |||
+ | example | ||
+ | --------------- | ||
+ | Pippèrr Gunther | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | Beispiel: Doppelte Wort in einem String entfernen | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select regexp_replace(' | ||
+ | ,' | ||
+ | ,' | ||
+ | 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: | ||
+ | |||
+ | <code sql> | ||
+ | -------------------------------------------- | ||
+ | -- alle treffer nach einander ausgeben lassen | ||
+ | -- | ||
+ | declare | ||
+ | -- test string | ||
+ | | ||
+ | | ||
+ | begin | ||
+ | -- anzahl möglicher treffer mit regexp_count ermitteln | ||
+ | for i in 1..regexp_count(v_value, | ||
+ | loop | ||
+ | dbms_output.put_line(' | ||
+ | ||regexp_substr(v_value, | ||
+ | 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: | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | # Schlecht - Laufzeit über 1,25 h | ||
+ | select count(1) from addresses where REGEXP_LIKE(mail_adress, | ||
+ | |||
+ | # Gut - Laufzeit ca 5 Minunte | ||
+ | |||
+ | select count(1) from addresses where REGEXP_LIKE(mail_adress,' | ||
+ | </ | ||
+ | |||
+ | 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,' | ||
+ | </ | ||
+ | |||
+ | === Function based index === | ||
+ | |||
+ | Auch für ein REGEXP kann ein " | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ==== 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> | ||
+ | | ||
+ | mother as ( | ||
+ | select 1 id from dual | ||
+ | union all | ||
+ | select 2 from dual | ||
+ | ) | ||
+ | , child as ( | ||
+ | select 1 id, ' | ||
+ | union all | ||
+ | select 2 , ' | ||
+ | ) | ||
+ | select m.id | ||
+ | , c.name | ||
+ | from mother m | ||
+ | , child c | ||
+ | where m.id = c.id(+) | ||
+ | and regexp_like(c.name (+), ' | ||
+ | / | ||
+ | |||
+ | ID NAME | ||
+ | ------------ ---- | ||
+ | 1 Tom | ||
+ | 2 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | === Einen YYYMD Datumsstring zerlegen === | ||
+ | |||
+ | Funktioniert leider so nicht, da hier sich Doppeldeutigkeiten ergeben: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | select regexp_substr(DMONAT,' | ||
+ | , case length(regexp_replace(DMONAT, | ||
+ | when 4 then regexp_replace(DMONAT, | ||
+ | when 3 then regexp_replace(regexp_replace(DMONAT, | ||
+ | when 2 then regexp_replace(regexp_replace(DMONAT, | ||
+ | end as month_date | ||
+ | , DMONAT | ||
+ | , regexp_replace(DMONAT, | ||
+ | from GPI_TABLE | ||
+ | group by DMONAT | ||
+ | / | ||
+ | |||
+ | |||
+ | YEAR MONTH_DATE | ||
+ | --------------- --------------- --------------- --------------- | ||
+ | 2014 0129 2014129 | ||
+ | 2014 0812 2014812 | ||
+ | |||
+ | </ | ||
+ | |||
+ | 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) | ||
+ | | ||
+ | is | ||
+ | v_return | ||
+ | v_email | ||
+ | v_ident_string varchar2(128): | ||
+ | v_count | ||
+ | begin | ||
+ | |||
+ | -- normalisiere | ||
+ | | ||
+ | |||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | | ||
+ | -- prüfe | ||
+ | | ||
+ | |||
+ | if v_count > 0 then | ||
+ | v_return: | ||
+ | else | ||
+ | -- falls nicht gültig verwerfe die E-mail Adresse | ||
+ | v_return: | ||
+ | end if; | ||
+ | | ||
+ | | ||
+ | |||
+ | end validateEMail; | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | === String in Elemente zerlegen === | ||
+ | |||
+ | In einer Log Tabelle sind Bestimmte Informationen in einer Liste aufgeführt die mit | getrennt ist. | ||
+ | |||
+ | Wie: " Column1: | ||
+ | |||
+ | Ziel ist es Value von Column3 | ||
+ | |||
+ | <code sql> | ||
+ | with data ( select ' | ||
+ | ) | ||
+ | | ||
+ | |||
+ | ---- | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | Beispiele => https:// | ||
+ | |||
+ | Oracle: | ||
+ | * http:// | ||
+ | * http:// | ||
+ | |||
+ | Standard Regular Expressions patterns: | ||
+ | * http:// | ||
+ | |||
+ | VI | ||
+ | * http:// | ||
+ | |||
+ | |||
+ | Online: | ||
+ | * https:// |
prog/sql_regular_expression.txt · Zuletzt geändert: 2022/11/08 17:19 von gpipperr