La fonction REGEXP_LIKE
La fonction « REGEXP_LIKE » est très utile pour effectuer des recherches dans des chaînes alphanumériques correspondant à un certain motif défini par une expression rationnelle (expression régulière).
REGEXP_LIKE ( CHAÎNE1, CHAÎNE2 [, PARAMETRE ] ) ;
CHAÎNE1 La chaîne à traiter.
CHAÎNE2 Une expression rationnelle qui permet de décrire un ensemble de chaînes. Les expressions rationnelles sont construites comme des opérations arithmétiques, en utilisant des opérateurs divers pour combiner des expressions plus petites.
PARAMETRE Paramètre de correspondance de l’expression rationnelle. Il peut avoir les valeurs suivantes :
i Ignorer les différences majuscules/minuscules dans le motif.
c Respecter les différences majuscules/minuscules dans le motif.
x Ignorer les espaces dans la chaîne à traiter.
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE commence par la chaîne '10' et finit par la chaîne 'pièces'.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE (QUANTITE,'^(10).*(pièces)$');
QUANTITE
------------------------------
10 boîtes x 12 pièces
10 sacs x 8 pièces
10 boîtes de 12 pièces
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE contient n’importe quel caractère ',' ou 'x'.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE (QUANTITE,'[,x]');
QUANTITE
------------------------------
10 boîtes x 20 sacs
10 boîtes x 12 pièces
24 cartons x 4 pièces
24 boîtes x 2 tartes
50 sacs x 30 saucisses
20 sacs x 4 pièces
10 sacs x 8 pièces
24 bouteilles (0,5 litre) 7%
48 bocaux de 170 g
12 bocaux de 225 g
12 bocaux de 340 g
24 bocaux de 225 g
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE contient la chaîne 'sacs' et la chaîne 'pièces'.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE (QUANTITE,'(sacs).*(pièces)');
QUANTITE
------------------------------
20 sacs x 4 pièces
10 sacs x 8 pièces
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE contient la chaîne 'carton' ou la chaîne 'pièces' ou la chaîne 'bouteilles' et commence par le caractère '2'.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE (QUANTITE,'^2.*(carton|pièces|bouteilles)');
QUANTITE
------------------------------
24 bouteilles (1 litre)
24 cartons x 4 pièces
24 cartons (500 g)
24 cartons (200 g)
24 bouteilles (70 cl)
24 bouteilles (1 litre)
24 cartons (50 g)
24 cartons (250 g)
24 bouteilles (12 onces) 3%
24 bouteilles (355 ml) 5%
24 cartons (200 g)
24 bouteilles (250 ml)
20 cartons (2 kg)
24 cartons (250 g)
24 pièces
24 bouteilles (500 ml)
20 sacs x 4 pièces
24 bouteilles (0,5 litre) 7%
24 paquets de 4 pièces
24 bouteilles de 35 cl 4
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE contient deux occurrences de la chaîne '100'.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE (QUANTITE,'(100.*){2}');
QUANTITE
------------------------------
100 pièces (100 g)
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE contient deux occurrences successives du caractère 'l' et deux occurrences successives du numéro '0'.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE (QUANTITE,'l{2,}.*0{2}');
QUANTITE
------------------------------
1 bouteille (500 ml) 21%
24 bouteilles (500 ml)
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE ne commence pas par un des numéros de la liste.
SQL> SELECT QUANTITE FROM PRODUITS WHERE REGEXP_LIKE (QUANTITE,'^[^1-3]');
QUANTITE
------------------------------
48 pots (6 onces)
4 boîtes (250 g)
50 cartons (300 g)
48 pièces
40 cartons (100 g)
50 sacs x 30 saucisses
48 tartes
La requête suivante est une sélection de la table FOURNISSEURS pour extraire toutes les lignes dont la colonne SOCIETE contient la chaîne 's.r.l'. En utilisant le paramètre de correspondance de l’expression rationnelle, il est possible d’ignorer les différences majuscules/minuscules dans le motif.
SQL> SELECT SOCIETE FROM FOURNISSEURS
2 WHERE REGEXP_LIKE ( SOCIETE, '(s[.]r[.]l)');
SOCIETE
----------------------------------------
Pasta Buttini s.r.l.
SQL> SELECT SOCIETE FROM FOURNISSEURS
2 WHERE REGEXP_LIKE ( SOCIETE, '(s[.]r[.]l)','i');
SOCIETE
----------------------------------------
Formaggi Fortini S.R.L.
Pasta Buttini s.r.l.
L’exemple suivant recherche toutes les sociétés clientes qui on une voyelle en double dans leur nom.
SQL> SELECT SOCIETE FROM CLIENTS
2 WHERE REGEXP_LIKE(SOCIETE, '([AEIOU])\1', 'i');
SOCIETE
----------------------------------------
Great Lakes Food Market
Queen Cozinha
Split Rail Beer Ale
The Big Cheese
Vins et alcools Chevalier
Blauer See Delikatessen
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE contient deux occurrences de la chaîne '100'.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE (QUANTITE,'(100).*\1');
QUANTITE
------------------------------
100 pièces (100 g)
La requête suivante est une sélection de la table CLIENTS pour extraire toutes les lignes dont la colonne ADRESSE contient deux occurrences consécutives d’un caractère de type alpha numérique.
SQL> SELECT ADRESSE FROM CLIENTS
2 WHERE REGEXP_LIKE(ADRESSE,'([0-9a-z])\1','i');
ADRESSE
-----------------------------------------------------
Kirchgasse 6
184, chaussée de Tournai
Romero, 33
Av. Brasil, 442
Carrera 22 con Ave. Carlos Soublette #8-35
1 rue Alsace-Lorraine
1900 Oak St.
12 Orchestra Terrace
Carrera 52 con Ave. Bolívar #65-98 Llano Largo
...
La requête suivante recherche la même colonne dans la même table mais pour deux fois deux occurrences consécutives d’un caractère alpha numérique dans la colonne ADRESSE.
SQL> SELECT ADRESSE FROM CLIENTS
2 WHERE REGEXP_LIKE'([0-9a-z])\1.*([0-9a-z])\2','i');
Carrera 22 con Ave. Carlos Soublette #8-35
Carrera 52 con Ave. Bolívar #65-98 Llano Largo
Heerstr. 22
South House300 Queensbridge
55 Grizzly Peak Rd.
Adenauerallee 900
Avda. de la Constitución 2222
Cerrito 333
Sierras de Granada 9993
La requête suivante recherche la même colonne dans la même table mais pour trois fois deux occurrences consécutives d’un caractère alpha numérique dans la colonne ADRESSE.
SQL> SELECT ADRESSE FROM CLIENTS WHERE REGEXP_LIKE(ADRESSE,
2 '([0-9a-z])\1.*([0-9a-z])\2.*([0-9a-z])\3','i');
ADRESSE
-----------------------------------------------------
Carrera 22 con Ave. Carlos Soublette #8-35
Adenauerallee 900
Afin de permettre la construction d'expressions encore plus complexes, Oracle supporte les classes de caractères. Grâce à cette classe de caractères, il va être possible d'écrire des expressions régulières extrêmement précises afin de retrouver seulement l'information voulue.
La requête suivante est une sélection de la table EMPLOYES pour extraire les employés qui ont un prénom qui contient le caractère 'e' suivi d’un ou deux caractères 'l' et suivi du caractère 'e' sans tenir compte des caractères accentués.
SQL> SELECT PRENOM FROM EMPLOYES
2 WHERE REGEXP_LIKE( PRENOM, '[[=e=]][l]+[[=e=]]');
PRENOM
------------------------------
Camille-Hélène
Gabrielle
Axelle
Isabelle
La requête suivante est une sélection de la table PRODUITS pour extraire toutes les lignes dont la colonne QUANTITE est formatée de la sorte : le début commence par deux ou trois occurrences d’un caractère numérique, ensuite un espace suivi d’un mot de trois à six caractères, un espace ensuite, et une parenthèse suivie de un à trois occurrences d’un caractère numérique, un espace, un caractère facultatif et finalement le caractère 'g' et une parenthèse.
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE(QUANTITE, '^[[:digit:]]{2,3}[[:space:]]
3 [[:alpha:]]{3,6}[[:space:]][\(][[:digit:]]{1,3}
4 [[:space:]][[:alpha:]]?(g[\)])$','x');
QUANTITE
------------------------------
12 pots (200 g)
20 boîtes (1 kg)
20 verres (450 g)
24 pots (250 g)
16 boîtes (500 g)
16 boîtes (2 kg)
15 unités (300 g)
24 pots (150 g)
32 boîtes (500 g)
100 sacs (250 g)
100 pièces (100 g)
10 verres (200 g)
15 pots (625 g)
SQL> SELECT QUANTITE FROM PRODUITS
2 WHERE REGEXP_LIKE(QUANTITE,
3 '^\d{2,3}\s\w{3,6}\s[\(]\d{1,3}\s\w?(g[\)])$','x');
QUANTITE
------------------------------
12 pots (200 g)
20 boîtes (1 kg)
20 verres (450 g)
24 pots (250 g)
16 boîtes (500 g)
16 boîtes (2 kg)
15 unités (300 g)
24 pots (150 g)
32 boîtes (500 g)
100 sacs (250 g)
100 pièces (100 g)
10 verres (200 g)
15 pots (625 g)