Mysql et la recherche : avec REGEXP

Publié dans SQL | Marqué avec , , ,
Share

Dans un précédent billet, on a vu comment effectuer une recherche assez poussée en MySQL avec LIKE. Mais LIKE n’est vraiment rien comparée à ce que l’on peut faire avec REGEXP OU RLIKE (RLIKE est un alias pour REGEXP. Il a été créé pour un souci de similitude avec mSQL). Et en même temps, je trouve que LIKE suffit la plupart du temps… REGEXP permet d’utiliser les expressions régulières pour retrouver une chaine de caractères dans une table SQL. Il existe 2 types de regex en PHP (petit nom des expressions régulières ^^) :

  • PCRE : issue du langage Perl, ces regex sont réputées difficiles, mais sont rapides et performantes
  • POSIX : mise en avant avec le PHP, ces regex se veut un peu plus simple que les PCRE, elles sont néanmoins moins performantes. MySQL utilise ce type de regex.

Sur son tutoriel de PHP, M@téo21 explique le fonctionnement des regex PCRE pour PHP. Si on comprend les PCRE, on comprend les POSIX, donc si vous n’y connaissez rien en regex, je vous conseille de lire son cours à ce sujet, il est particulièrement bien fait : Cours sur les regex de M@téo21.

Utiliser REGEXP

Une fois que l’on maitrise les regex (voir ci-dessous), utiliser REGEXP n’est pas un problème. Un exemple :

SELECT * FROM table WHERE champ REGEXP "<regex>"


Cette requête tiendra compte de la casse. Pour ne pas en tenir compte, il faut utiliser BINARY, comme avec LIKE :

SELECT * FROM table WHERE champ REGEXP BINARY "";

Donc, par exemple, si je souhaite chercher tous les champs contenant « poussette » (par exemple le champ « j’aime les poussettes », j’utiliserai la requête suivante :

SELECT * FROM table WHERE champ REGEXP "poussette";

Regex classiques

Les classes de caractères [...]

Les classes de caractères permettent de préciser le choix entre plusieurs caractères ou un intervalle de caractères. Par exemple [abcd] correspondra à la lettre a ou la lettre b ou c ou d, donc garage, bébé, ou coca-cola matcheront par exemple. On aurait pu aussi l’écrire [a-d]. Donc de la même manière [a-z] permet de matcher une lettre entre a et z. Et on peut faire des grands mixes : [a-z0-9_ù]… Pour utiliser le tiret ( - ), il faut le placer au début à la fin de l’intervalle : [a-z-]. Compliquons un peu les choses. Pour dire que le mot NE doit PAS contenir des caractères, on utilise [^…]. Par exemple, [^a-z] signifie « pas de lettre ».

Début-fin de chaines, OU et parenthèses

Pour préciser qu’un champ doit commencer par une chaine de caractères, on utilise ^, pour préciser qu’un champ doit se terminer par une chaine de caractères, on utile $. Par exemple ^[a-z] matchera tous les champs commençant par une lettre entre a et z. Et [0-9]$ matchera tous les champs finissant par un chiffre. Pour préciser un choix entre eux chaines de caractères (ce qui correspond à un OU), on utilise le pipe ( | ). Par exemple pour chercher les champs contenant chat ou chien, on utilisera chien|chat. Les parenthèses nous permettent de créer des blocs pour faire exactement ce qu’on veut. Faisons un grand mixe. On cherche les champs commençant par chien ou chat et se terminant par s ou x… C’est parti : ^(chien|chat)[sx]$.

Quantificateurs {...}, ., *, +, ?

Les quantificateurs de préciser le nombre de motifs.

  • * signifie que ce qui précède peut apparaitre 0 ou plusieurs fois
  • + signifie que ce qui précède peut apparaitre 1 ou plusieurs fois
  • ? signifie que ce qui précède peut apparaitre 0 ou 1 fois
  • {3} signifie que ce qui précède doit apparaitre 3 fois
  • {3,6} signifie que ce qui précède doit apparaitre 3, 4, 5 ou 6 fois
  • {3,} signifie que ce qui précède doit apparaitre 3 ou plusieurs fois

Quelques exemples…

  • Regex : gr[aoi]?s Mots matchant : gras, gros, gris ou grs
  • Regex : gr[aoi]+s Mots matchant : graoioaiaos ou graoiiiios par exemple
  • Regex : gr[aoi]*s Mots matchant : grs, graoioaiaos ou graoiiiios par exemple
  • Regex : gr[aoi]{3}s Mots matchant : graois ou grooos par exemple
  • Regex : gr[aoi]{1,2}s Mots matchant : gras, groos, ou groas par exemple
  • Regex : gr[aoi]{3,}s Mots matchant : graois, groiaoaiaoais par exemple

Comme d’habitude, pour utiliser des caractères utilisés dans le langage, on les échappe avec un backslash ( \ ) ou plutôt 2 (d’après la doc, mais je ne vois pas pourquoi…) ou un autre caractère à préciser avec ESCAPE "caractère".

Autres

Un retour chariot (un retour à la ligne) correspond à \n et une tabulation à \t. Très important, le point ( . ), il correspond à tous les caractères sauf le retour chariot (retour à la ligne) ! Donc, pour matcher tous les champs, même le champ vide, sauf les champs contenant seulement un retour chariot (ce qui est un peu idiot) :

SELECT * FROM table WHERE champ REGEXP ".*";

Mais plus intéressant, pour matcher les champs contenant exactement 7 caractères :

SELECT * FROM table WHERE champ REGEXP "^.......$";

Regex un peu particulières

Tout ce que nous venons de voir est assez classique pour des regex. Disons que personnellement, je connaissais déjà quoi ! Mais il en a d’autres en MySQL, et aussi quelques raccourcis.

Classe de caractères

On peut raccourcir ou clarifier une regex en utilisant des classes de caractères qui, en un seul mot clef, correspondent à des regex. Par exemple [[:alnum:]] correspond aux caractères alphanumériques, donc à la regex [a-z0-9-] (plus quelques autres peut-être). Cela ne raccourci pas forcément le code, mais ça peut servir. La construction est donc [[:mot clef:]], et voici la liste des mots clefs :

  • alnum : Caractères alpha-numériques
  • alpha : Caractères alphabétiques
  • blank : Caractères espace
  • cntrl : Caractères de contrôle
  • digit : Chiffres
  • graph : Caractères graphiques
  • lower : Minuscules
  • print : Caractères graphiques ou espaces
  • punct : Ponctuation
  • space : Espace, tabulation, nouvelle ligne et retour chariot
  • upper : Majuscules
  • xdigit : Chiffres hexadécimaux
  • < > : non précédé par un caractère alphanumérique ou un _. On utilise parfois [[:<:]]mot[[:>:]] pour vérifier qu’on matche réellement le mot « mot » et non le mot « motel ». Le problème est que si « mot » se trouve en début ou en fin de chaine, il n’est pas repérée par cette regex. La solution consiste à utiliser plutôt quelque chose comme : ([[:space:]]|[_-]|^)mot([[:space:]]|[_-]|$)

Eh bien voilà, nous en avons fini avec la commande REGEXP ! Il y a encore pas mal de petits détails à connaître et qui peuvent servir, mais c’est déjà pas mal ! Pour s’entrainer facilement, il est de bon ton d’ouvrir un terminal MySQL (ou d’utiliser PHPMyAdmin ou PgMyAdmin, ou…) et de faire des requêtes du type :

SELECT 'groiaois' REGEXP 'gr[aoi]+s';

Si le mot match, SQL renvoie 1, sinon il renvoie 0. (en l’occurrence ici, il renverra 1)

P.S : Ce billet a été un peu long et délicat à écrire, alors si vous trouvez des erreurs, surtout n’hésitez pas à le faire remarquer. Merci d’avance !

Pour aller plus loin

6 réponses à Mysql et la recherche : avec REGEXP

  1. Bonjour,

    À la fin du paragraphe « Les classes de caractères […] » il y a …

    [^a-z] signifie « ps de lettres », je crois que cela dois signifier « pAs de lettre » (avec un a et pas de s car il n’y en a aucune…).

    Bonne journée!

    Jonathan L. Roch

  2. BN

    Merci ! C’est corrigé.

  3. Naah… Il reste le « s »… Dans mon royaume : pas est égal à zéro et zéro est égal à « pas de pluriel »…

    if ( $i >= 2 ) { $findumot = « s »; } else { $findumot = « pas de pluriel »; }…

    Jonathan L. Roch

    :boutade)

    S. R.

  4. BN

    C’est mieux comme ça ? ^^

    Merci.

  5. Je ne voulais pas faire naître de ressentiment… Je trouve tes explications excellentes et tes textes me sont bien utiles! C’est que ma femme est une « pro » du français et elle me bat à coup d’étoile-du-jour (http://www.google.com/images?um=1&a…) si je fais une faute… ;-P

    J’ai un peu la même philosophie de vie que toi… Apprendre à apprendre c’est le pied! Une fois que c’est entré, ça ne peut que s’améliorer…

    Si les gens appliquaient ton principe de 30 minutes par jour, il y aurait moins de conflit et ainsi beaucoup plus de profit intellectuel.

    De mon coté, j’ai cette expression tatouée au fond du cerveau : Donne un poisson à un homme et tu le nourriras pour une journée. Apprends-lui à pêcher et tu le nourriras pour la vie…

    Merci à toi de prendre le temps de nous enseigner à pêcher…

    Jonathan L. Roch

  6. merci
    j’ai un problème avec les accents « é » et « e »

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*