16 octobre 2014

Des expressions régulières sous Oracle

Dans un "LIKE" classique les seuls métacaractères sont "%" (n caractères quelconques) et "_" (un caractère quelconque).

Mais Oracle permet aussi d'utiliser dans les clauses WHERE une condition LIKE de type REGEXP (expression rationnelle comme celle de la commande Unix "grep"). Ainsi pour lister précisément les tables des structures de données HR et les tables M71 M81 et M91 l'ordre SELECT peut prendre la forme suivante :

SQL> select TABLE_NAME from ALL_TABLES where OWNER='HR' and
    (REGEXP_LIKE(TABLE_NAME,'^[X-Z][A-Z0-9]{3}$')
     -- tables sur 4 caracteres dont le premier vaut X, Y ou Z
  or REGEXP_LIKE(TABLE_NAME,'^[A-Z0-9]+M[789]1$')
     -- tables de nom alphanumerique suffixes M71 M81 ou M91
  or REGEXP_LIKE(TABLE_NAME,'^[X-Z][A-Z0-9]LG1[02]$')
     -- tables de 6 caracteres suffixees LG10 ou LG12 dont le premier vaut X, Y ou Z
  or REGEXP_LIKE(TABLE_NAME,'^[X-Z][A-Z0-9]TD1[12]$')
     -- tables de 6 caracteres suffixes TD11 ou TD12 dont le premier vaut X, Y ou Z
  );

Par défaut
  • le REGEXP_LIKE est sensible à la casse (indiquez en 3ème paramètre un 'i' pour l'ignorer),
  • le "." signifie "caractère quelconque (indiquez 'n' pour qu'il signifie "new line"),
  • chaque ligne est considérée comme distincte des autres (indiquez 'm' pour que la source soient considérées comme une ligne unique),
  • par défaut les "blancs" de la REGEXP sont considérés tels quels (indiquez 'x' pour les ignorer).
Il existe d'autres commandes basées sur ce principe (REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_COUNT). Il faut juste "un peu" de temps pour digérer toutes les règles !

SQL> select NMPRES, REGEXP_REPLACE(NMPRES,'(\S+), (\S+)','\2 \1') as NMPRE2 from ZY3Y where NMPRES like '%,%';

NMPRES                 
NMPRE2
----------------------- --------------------------
DAVIS, Linda            Linda DAVIS
FELTON, James
           James FELTON
RUSSO, Timothy
          Timothy RUSSO

Plus d'informations sur le site Oracle.