Espressioni Regolari in Oracle SQL e PL/SQL

This article is also available in English here.

A partire dalla versione 10 di Oracle sono state aggiunte ad SQL e PL/SQL nuove funzionalità che consentono di utilizzare le espressioni regolari in maniera conforme a quanto definito dallo standard POSIX.
In particolare nella versione 10g sono state introdotte le funzioni REGEXP_INSTR, REGEXP_REPLACE e REGEXP_SUBSTR e la condizione REGEXP_LIKE.
Successivamente nella versione 11g è stata aggiunta anche la funzione REGEXP_COUNT. Vediamo come possiamo utilizzarle.

REGEXP_INSTR funziona più o meno come la INSTR: serve a cercare, all’interno di una stringa data, una sottostringa che varifica il pattern della regex data in input.
Se non trova nessuna sottostringa che verifica la regex ritorna 0.
Il prototipo della funzione è

REGEXP_INSTR (source_string, pattern, position, occurrence, return_option, match_parameter)

Dove source_string è la stringa in cui cercare;
pattern è l’espressione regolare;
position è la posizione a partire dalla quale bisogna cercare;
occurrence è l’occorrenza richiesta;
return_option può valere 0 se si desidera la posizione del primo carattere della sottostringa trovata, 1 se si desidera la posizione del primo carattere successivo alla sottostringa trovata;
match_parameter è un flag che può valere:

i (ricerca case-insensitive),

c (ricerca case-sensitive),

n (il carattere jolly ‘.’ trova anche il ritorno a capo),

m (interpreta la stringa in modalità multi-linea)

Solo i primi due parametri sono obbligatori.

Facciamo un esempio: cerchiamo nella stringa ‘Questa è una stringa di prova che mi consente di illustrare le regex in Oracle’ la seconda parola composta di due lettere:

WITH T AS (
SELECT 'Questa è una stringa di prova che mi consente di illustrare le regex in Oracle' str from dual)
Select str, REGEXP_INSTR(str,'(^|\ )[[:alpha:]]{2}($|\ )', 1, 2)+1 pos
FROM t;

STR                                                                                   POS
------------------------------------------------------------------------------ ----------
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle         35

Passiamo alla REGEXP_REPLACE, consente di sostiutire una stringa trovata mediante espressione regolare con un’altra stringa data:

REGEXP_REPLACE(source_string, pattern, replace_string, position, occurrence, match_parameter)

Dove source_string è la stringa in cui effettuare la ricerca e la sostiturione;
pattern è l’espressione regolare;
replace_string è la stringa da sostituire a quanto trovato mediante il pattern;
position è la posizione a partire dalla quale bisogna cercare;
occurrence è l’occorrenza richiesta;
match_parameter come nella REGEXP_INSTR.

Se quindi vogliamo sostituire, nella stringa dell’esempio precedente, la seconda parola di due caratteri con una X, dobbiamo fare:

WITH T AS (
SELECT 'Questa è una stringa di prova che mi consente di illustrare le regex in Oracle' str from dual)
Select REGEXP_REPLACE(str,'(^|\ )[[:alpha:]]{2}($|\ )', ' X ',1, 2) newstr
FROM t;

NEWSTR
-----------------------------------------------------------------------------
Questa è una stringa di prova che X consente di illustrare le regex in Oracle

La terza funzione disponibile è la REGEXP_SUBSTR che consente di estrarre una sottostringa:

REGEXP_SUBSTR (source_string , pattern, position, occurrence, match_parameter)

Dove source_string è la stringa in cui effettuare la ricerca e da cui estrarre la sottostringa;
pattern è l’espressione regolare;
position è la posizione a partire dalla quale bisogna cercare;
occurrence è l’occorrenza richiesta;
match_parameter come nella REGEXP_INSTR.

Quindi per estrarre la seconda parola da due caratteri:

WITH T AS (
SELECT 'Questa è una stringa di prova che mi consente di illustrare le regex in Oracle' str from dual)
Select str, trim(REGEXP_SUBSTR(str,'(^|\ )[[:alpha:]]{2}($|\ )',1, 2)) substr
FROM t;

STR                                                                            SUBSTR
------------------------------------------------------------------------------ ------
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle mi

La REGEXP_COUNT ci consente di contare le occorrenze nella source string che verificano il pattern:

REGEXP_COUNT (source_char , pattern, position, match_param) 

Dove source_char è la stringa in cui effettuare la ricerca;
pattern è l’espressione regolare;
position è la posizione a partire dalla quale bisogna cercare;
return_option come nella REGEXP_INSTR.

Quindi per ottenere il numero di parole di due caratteri:

WITH T AS (
SELECT 'Questa è una stringa di prova che mi consente di illustrare le regex in Oracle' str from dual)
Select str, REGEXP_COUNT(str,'(^|\ )[[:alpha:]]{2}($|\ )',1) count
FROM t;

STR                                                                                 COUNT
------------------------------------------------------------------------------ ----------
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle          5

Possiamo usare in modalità combinata la REGEXP_COUNT e la REGEXP_SUBSTR (ed un trucchetto della connect by) per estrarre in un colpo solo tutte le parole di 2 caratteri:

WITH T AS (
SELECT 'Questa è una stringa di prova che mi consente di illustrare le regex in Oracle' str from dual)
Select str, trim(REGEXP_SUBSTR(str,'(^|\ )[[:alpha:]]{2}($|\ )',1, level)) substr
FROM t connect by level<=REGEXP_COUNT(str,'(^|\ )[[:alpha:]]{2}($|\ )',1);

STR                                                                            SUBSTR
------------------------------------------------------------------------------ ------
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle di
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle mi
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle di
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle le
Questa è una stringa di prova che mi consente di illustrare le regex in Oracle in

Finite le funzioni passiamo alla REGEXP_LIKE, che è una condizione, cioè torna boolean e può essere utilizzata nella WHERE o nella HAVING di una query:

REGEXP_LIKE (source_string, pattern, match_parameter)

Dove source_string è la stringa in cui effettuare la ricerca;
pattern è l’espressione regolare;
match_parameter come nella REGEXP_INSTR.

REGEXP_LIKE(str,ptrn,mp) è logicamente equivalente a REGEXP_INSTR(str,ptrn,1,1,mp)>0

Tiriamo per esempio fuori tutte le stringhe che hanno almeno una parola da due caratteri:

WITH T AS (
SELECT 'Stringa senza parole di2' str from dual union
SELECT 'prima Stringa con parole di 2 caratteri' from dual union
SELECT 'seconda Stringa con parole di 2 caratteri' from dual union
SELECT 'Altra Stringa senza parole di2' from dual
)
Select str
from t
where REGEXP_LIKE(str,'(^|\ )[[:alpha:]]{2}($|\ )');

STR
-----------------------------------------
prima Stringa con parole di 2 caratteri
seconda Stringa con parole di 2 caratteri

Come detto sarebbe stato del tutto equivalente scrivere:

WITH T AS (
SELECT 'Stringa senza parole di2' str from dual union
SELECT 'prima Stringa con parole di 2 caratteri' from dual union
SELECT 'seconda Stringa con parole di 2 caratteri' from dual union
SELECT 'Altra Stringa senza parole di2' from dual
)
Select str
from t
where REGEXP_INSTR(str,'(^|\ )[[:alpha:]]{2}($|\ )')>0;

Questo è tutto sulle espressioni regolari in Oracle SQL e PL/SQL!

Alla prossima,
Massimo

Tag: Espressioni regolari, oracle, REGEXP, SQL