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

Zur Sammlung "Reguläre Ausdrücke"

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

Go to the collection "regular expressions" (german language)

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

Binärdatei per SQL-Skript als BLOB erzeugen: Funktion zum Ausprobieren

English title: Generate SQL Script to create a BLOB from binary content

Vor einiger Zeit hatte ich das Blog-Posting zu "BLOBs als PL/SQL-Skript exportieren" veröffentlicht. Mit der darin vorhandenen PL/SQL-Funktion kann man aus einer Binärdatei (Bild, Dokument, etc) ein PL/SQL-Skript generieren, welches diese Binärdatei als BLOB generiert. Das ist dann nützlich, wenn man keine Möglichkeit hat, exp oder imp zu verwenden und nur Skripte laufen lassen kann.

Diese Funktion habe ich nun auf apex.oracle.com bereitgestellt. Ihr könnt dort eine Datei hochladen und bekommt dann ein PL/SQL-Skript zurück ... Probiert es einfach mal aus ...

Some time ago I published a blog posting how to generate a SQL script which creates a BLOB from binary content. The contained function accepts a blob and returns a pure PL/SQL script which generates that blob. This might be useful in cases where BLOB data has to be inserted into tables and exp or imp are not available. The blob is created with "pure" PL/SQL.

I now published this function as an APEX app on apex.oracle.com. After uploading a file you get the PL/SQL script which generates that file as a BLOB in your database.

Kreuztabellen (pivot tables) mal anders genutzt ...

English title: Creative usage of Pivot tables (matrix reports)

Kürzlich hatte ich eine recht interessante Frage von einem Kunden bekommen - ich hätte vorher zwar niemals gedacht, dass man sowas gebrauchen kann, aber danach ist mir klargeworden, dass alle Bus-, Zug- und Flugpläne typischerweise so aufgebaut werden: Wie kann man das Ergebnis einer SQL-Abfrage (eine Spalte) auf 10 Spalten verteilen? Also: Angenommen, man hätte folgende Ergebnismenge ...

Short time ago I was asked a question - I had never thought that someone really need this stuff, but then it got clear that every bus, train oder flight schedules are printed that way: How can the result of a SQL query (one column) be distributed over 10 (or say: X) columns?. Let's assume we have the following query result ...

TEXT
--------------------
17.12.80: SMITH
20.02.81: ALLEN
22.02.81: WARD
02.04.81: JONES
01.05.81: BLAKE
09.06.81: CLARK
08.09.81: TURNER
28.09.81: MARTIN
17.11.81: KING
03.12.81: FORD
03.12.81: JAMES
23.01.82: MILLER
09.12.82: SCOTT
12.01.83: ADAMS

... dann soll die gewissermaßen wie ein "Busfahrplan" aufbereitet werden:

... then we want this have returned in the manner of a bus schedule:

     ZEILE 1                    2                    3                    4                    5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         0 17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES      01.05.81: BLAKE
         1 09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING       03.12.81: FORD
         2 03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS

... und natürlich kann man das prozedural lösen (PL/SQL Prozeduren, Table Functions), aber eine SQL-Abfrage wäre besser - diese könnte man dann direkt in einem Reporting-Werkzeug oder als Grundlage für eine PDF-Ausgabe verwenden. Und da ich hier nun im Zug sitze, ist das die optimale Gelegenheit, ein Blog-Posting dazu zu schreiben.

And of course we can do this with procedural logic (PL/SQL- or Table Functions). But for now we want to achieve this with just a SQL query. And this has advantages. A pure SQL query can directly be used by reporting tools or PDF generators. So its an interesting question: And since I'm sitting in a train for the next two hours now this is a good opportunity to write a blog posting about it.

Man könnte sich dem Problem so nähern, dass man für jeden Wert erstmal eine Zeile und eine Spalte generiert. Das ist mit Divisions- und Modulo-Operationen auch ganz einfach: Der erste Schritt (für 5 Spalten) sähe also so aus:

An approach to the problem is to generate line and column numbers for our "matrix" report. This is an easy task - we have modulo and division operators in the SQL language. The first step (for five columns) then looks like this:

  SELECT 
    trunc((rownum - 1) / 5) as zeile,   
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )

     ZEILE     SPALTE TEXT
---------- ---------- --------------------
         0          1 17.12.80: SMITH
         0          2 20.02.81: ALLEN
         0          3 22.02.81: WARD
         0          4 02.04.81: JONES
         0          0 01.05.81: BLAKE
         1          1 09.06.81: CLARK
         1          2 08.09.81: TURNER
         1          3 28.09.81: MARTIN
         :          : :

Durch die Modulo-Operation hat die letzte Spalte die kleinste Nummer (0), aber das soll uns jetzt nicht weiter stören ... was jetzt noch übrig bleibt, ist das Verteilen der Werte auf die einzelnen Spalten - also eine Pivot-Operation. Und zum Glück gibt es in Oracle11g dafür die SQL Pivot Klausel:

Due to the modulo operation the last (fifth) column has the smallest number (zero) but this is so far no issue ... the rest of the problem is now a typical pivoting operation. And we're lucky: in Oracle11g there is the SQL PIVOT clause:

SELECT * FROM (
  SELECT 
    trunc((rownum - 1) / 5) as zeile,   
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )
) 
PIVOT (
  min(text) FOR spalte IN  (
    1 as "1", 
    2 as "2", 
    3 as "3", 
    4 as "4", 
    0 as "5"
  )
)
order by zeile
/

... was genau zum gewünschten Ergebnis führt ...

... which leads us exactly to the correct result ...

     ZEILE 1                    2                    3                    4                    5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         0 17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES      01.05.81: BLAKE
         1 09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING       03.12.81: FORD
         2 03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS

Mit Oracle11g ist das Problem also schon gelöst: Doch was, wenn das auf einer älteren Version laufen soll ...? Nun, zu Kreuztabellen (Pivot tables) vor Oracle11g hatte ich schonmal ein Blog Posting - die Funktion getPivotSql, die ich dort vorgestellt hatte, kann auch hier weiterhelfen. Zuerst erzeugen wir also mit getPivotSql das Kreuztabellen-SQL für obige Abfrage ...

So Oracle11g provides an easy solution for this problem. But what if we don't have Oracle11g. I had a blog posting about pivot tables or matrix reports in previous versions in the past. The PL/SQL function getPivotSql can help us here - it generates the final SQL query for us ...

select getpivotsql(
  'SELECT 
    trunc((rownum - 1) / 5) as zeile,   
    mod(rownum, 5)          as spalte,
    hiredate || '': ''||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )',
  'SPALTE',
  'VARCHAR2',
  null,
  null,
  null,
  5,
  'ZEILE',
  'TEXT',
  'MAX'
) from dual;

with data as (
  SELECT
    trunc((rownum - 1) / 5) as zeile,
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )
), x_dist_values as (
  select distinct SPALTE val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.ZEILE,
MAX(  case when x.zeile = 1 then TEXT else null end) as "VALUE[1]",
MAX(  case when x.zeile = 2 then TEXT else null end) as "VALUE[2]",
MAX(  case when x.zeile = 3 then TEXT else null end) as "VALUE[3]",
MAX(  case when x.zeile = 4 then TEXT else null end) as "VALUE[4]",
MAX(  case when x.zeile = 5 then TEXT else null end) as "VALUE[5]"
from data
join x_values_rownum x on (data.SPALTE = x.val)
group by data.ZEILE

Führt man dieses SQL aus, bekommt man ...

Executing this gives us ...

     ZEILE VALUE[1]             VALUE[2]             VALUE[3]             VALUE[4]             VALUE[5]
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         1 03.12.81: FORD       09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING
         2                      03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS
         0 01.05.81: BLAKE      17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES

... schon nahe dran, aber noch nicht ganz korrekt - in der Tat muss man das SQL noch ein wenig nachbearbeiten. Erstmal nach Zeilen sortieren; dazu wird ein order by zeile ans Ende gehangen. Und die Spalten müssen auch anders sortiert werden - in der Oracle11g-Abfrage oben sieht man es ja: Die Spalte, die an letzter Stelle stehen soll, hat den Modulo "0" (Null). Dadurch stellt die Funktion getPivotSql sie an den Anfang.

... which is not exactly what we want - the resulting SQL query has to adjusted a little bit. The first thing is simple: Add an order by zeile at the end to have correct row order. The second issue is the column ordering. As seen above the columns are dermined by the modulo operation. And for the last column the modulo is zero (0). So the getPivotSql function places this column as the first one.

Also ändern wir das SQL ein diesen zwei Stellen um ...

So change the SQL query a little bit ...

with data as (
  SELECT
    trunc((rownum - 1) / 5) as zeile,
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )
), x_dist_values as (
  select distinct SPALTE val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.ZEILE,
MAX(  case when x.zeile = 2 then TEXT else null end) as "VALUE[1]",
MAX(  case when x.zeile = 3 then TEXT else null end) as "VALUE[2]",
MAX(  case when x.zeile = 4 then TEXT else null end) as "VALUE[3]",
MAX(  case when x.zeile = 5 then TEXT else null end) as "VALUE[4]",
MAX(  case when x.zeile = 1 then TEXT else null end) as "VALUE[5]"
from data
join x_values_rownum x on (data.SPALTE = x.val)
group by data.ZEILE
order by data.ZEILE

Und führt man das aus, kommt auch in Oracle9i oder Oracle10g das korrekte Ergebnis heraus.

... which leads us (also in Oracle9i or Oracle10g) to the result we wanted to achieve.

     ZEILE VALUE[1]             VALUE[2]             VALUE[3]             VALUE[4]             VALUE[5]
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         0 17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES      01.05.81: BLAKE
         1 09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING       03.12.81: FORD
         2 03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS

Viel Spaß damit - vielleicht können es ja noch mehr gebrauchen ...

Perhaps more people find this useful - have fun!