23. Februar 2009

Reguläre Ausdrücke ... in der Datenbank!

English title: Regular Expressions ... in the database!

Seit Oracle 10.1 gibt es ja die Unterstützung für reguläre Ausdrücke direkt im SQL-Sprachumfang der Datenbank. So stehen folgende SQL- und PL/SQL-Funktionen bereit:
  • REGEXP_LIKE: Das ist die LIKE-Variante für reguläre Ausdrücke. Arbeitet wie das normale SQL LIKE, man kann aber anstelle von % oder _ reguläre Ausdrucke angeben.
  • REGEXP_INSTR: Gibt die Stelle (Zeichenposition), an welcher ein Match aufgetreten ist, zurück.
  • REGEXP_SUBSTR: Gibt den Teilstring, der auf den regulären Ausdruck passt, zurück.
  • REGEXP_REPLACE: Find & Replace mit regulären Ausdrücken - der Backslash kennzeichnet einen Teil des regulären Ausdrucks (\1, \2 usw).
  • (Oracle11g) REGEXP_COUNT: Gibt an, wie oft der reguläre Ausdruck sich in der Zeichenkette wiederfindet.
Regular Expressions (regexp) are part of the SQL language since Oracle 10.1. So you can use the following PL/SQL and SQL functions:
  • REGEXP_LIKE: This is the LIKE variant with regular expressions. It works analog to SQL LIKE but you can use regexp instead of the "classic" % or _ syntax
  • REGEXP_INSTR: Returns the string position (index) at which the regexp matches.
  • REGEXP_SUBSTR: Returns the matching substring.
  • REGEXP_REPLACE: Find & Replace with regexp. The backslash identifies a regexp part (\1, \2 and so on).
  • (Oracle11g) REGEXP_COUNT: Returns how often the regexp matches the input string.
Reguläre Ausdrücke sind (die meisten wissen es) unglaublich mächtig, Dinge, die ansonsten einige Zeilen PL/SQL-Code erfordern würden, finden in einem kompakten regulären Ausdruck Platz. Und das beste ist: Man kann diese Funktionen nicht nur in SQL-Abfragen, sondern auch bspw. in einem Check-Constraint nutzen.
As the most know regular expressions are very useful - problems which otherwise would require some programming (PL/SQL in our case) can be solved with a brief, compact syntax. And the best is that regexp support is built in the database kernel - which means that regular expressions can also be used for declaring check constraints - here is an example:
create table hardware_ip(
 rechner_id   number(10),
 ip_addr      varchar2(15),
 constraint ck_ip_addr check(
   regexp_like(
     ip_addr, 
     '^[[:digit:]]{1,3}\.[[:digit:]]{1,3}\.[[:digit:]]{1,3}\.[[:digit:]]{1,3}$'
   )
 )
);

Table created.

insert into hardware_ip values (0, '10.28.1721.9')
*
ERROR in line 1:
ORA-02290: CHECK-Constraint (PARTNER.CK_IP_ADDR) violated

SQL> insert into hardware_ip values (0, '10.28.172.9');

1 row created.
"Vorlagen" für reguläre Ausdrücke findet man im Internet - und in Application Express. Der Bereich Validations, wo es darum geht, Eingabevalidierungen für Formularelemente zu hinterlegen, enthält auch einen Abschnitt für reguläre Ausdrucke. Dort findet man eine ganze Menge fertiger Ausdrücke ...
The internet is full of regexp samples. Also Oracle's Application Express leverages regexp in its form validations. The developer can declare a regexp validation - and APEX offers some regexp examples out-of-the-box.
So kann man bspw. mit ^http[s]?://[-a-zA-Z0-9_.:]+[-a-zA-Z0-9_:@&?=+,.!/~*'%$]*$ prüfen, ob eine korrekte URL eingegeben wurde. Analog lassen sich Eingabeformate für Telefonnummern, KFZ-Kennzeichen und vieles mehr durchsetzen
^http[s]?://[-a-zA-Z0-9_.:]+[-a-zA-Z0-9_:@&?=+,.!/~*'%$]*$, for example, checks whether the input string is correct URL syntax. Phone numbers, registration numbers and other pattern matching can easily achieved with regular expressions.
Einmal kam ein Kollege zu mir, der wissen wollte, wie man am besten die Hausnummer aus einer Adresse extrahieren kann - und auch hierfür eignen sich Reguläre Ausdrücke hervorragend - das folgende Beispiel zeigt auch gleich, wie man REGEXP_REPLACE nutzen kann ...
I once had a discussion with a collegue how to extract the house number out of a postal address. And this should also work for (german) house number suffixes (1a, 10b, etc). We worked out the following regexp - and this example also shows how to use REGEXP_REPLACE.
SQL> select regexp_replace('Wichtige-Person Str. 25a-z','([-A-Za-Z_. ()&]*)([0-9]{0,5})([-a-zA-Z ]*)','\1') from dual;

REGEXP_REPLACE('WICHT
---------------------
Wichtige-Person Str.

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.06
PARTNER@[192.168.2.140:1521/orcl.doag.org]
SQL> select regexp_replace('Wichtige-Person Str. 25a-z','([-A-Za-Z_. ()&]*)([0-9]{0,5})([-a-zA-Z ]*)','\2') from dual;

RE
--
25

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.01
SQL> select regexp_replace('Wichtige-Person Str. 25a-z','([-A-Za-Z_. ()&]*)([0-9]{0,5})([-a-zA-Z ]*)','\3') from dual;

REG
---
a-z
Und da es in den meisten Fällen so ist, dass irgendjemand da draußen den regulären Ausdruck, den man gerade sucht, schon hat, habe ich eine Applikation auf apex.oracle.com bereitgestellt. Hier würde ich gerne reguläre Ausdrücke, die jeder gebrauchen kann, sammeln. Einige Beispiele habe ich bereits eingestellt und werde es auch künftig tun. Ich würde mich freuen, wenn wir hier eine schöne "Bibliothek" zusammenstellen könnten ...
And since in most cases anyone out there has already worked out the regexp I'm currently working on I created a public APEX application on apex.oracle.com. I'd like to collect useful regular expressionsone's here and would be happy if the community manages to build up a litte regexp library ...
Mehr zum Thema findet sich in der Oracle-Dokumentation: Application Developers' Guide: Regular Expressions
More information on this is in the documentation: Application Developers' Guide: Regular Expressions

Kommentare:

Anonym hat gesagt…

Schöne Idee.

In Dresden haben wir übrigens so tolle Straßennamen wie "Straße des 17. Juni" oder "1. Steinweg". Da ist dann der vorgeschlagene Ausdruck am Ende seiner Kunst.

Gruß

Ralf

Carsten Czarski hat gesagt…

Hallo,

das stimmt wohl; er würde ebenso bei amerikanischen Adressen versagen. Ich schau's mir mal an; vielleicht kann man ihn nochmal ein wenig verbessern ...

-Carsten

Anonym hat gesagt…

Hallo,

Ich will ja nicht nur meckern ;-)

Unter der Maßgabe, dass es eine Hausnr. gibt, sollte das doch ganz gut funktionieren:
select regexp_replace('1. Straße des 17. Juni 12b','(.+?)([0-9]{1,5})([-a-zA-Z ]*?)$','\2') from dual;

Gruß

Ralf

Carsten Czarski hat gesagt…

Hallo,

das sieht doch gut aus; ich habe ihn allerdings noch ein wenig verändert ...

^(.+?)([-0-9]+?)([-a-zA-Z ]*?)$

... so dass auch Adressen wie "1. Straße des 17. Juni 13-19 richtig bearbeitet werden.

Man sieht, dass Zusammenarbeit bei regulären Ausdrücken nur hilfreich sein kann ... daher die Idee mit der "Bibliothek"

-Carsten

Andreas E. hat gesagt…

Hallo Herr Czarski,

dennoch gibt es noch paar Problemkinder mit Ihrer Lösung. Z.B. Strasse ganz ohne Hausnummer, oder Wechsel von Groß- und Kleinschreibung:

A) Heinrich3-Böll-Str.
B) Heinrich-böll-Str
C) Heinrich-Böll-Str.

3 Unterschiede Ergebnisse für diese Datensätze.

Das / sollte bei der Betrachtung für Hausnummernbereiche ebenfalls berücksichtigt werden.

Viele Grüße

Beliebte Postings