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

en savoir

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)