Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_regular_expression

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:

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: 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;
PatternErklärungBeispielfindet
.
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?

 Übersicht über den regulären Ausdruck zum finden doppelter Daten


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

Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
"Autor: Gunther Pipperr"
prog/sql_regular_expression.txt · Zuletzt geändert: 2022/11/08 17:19 von gpipperr