MySQL/Regex

En informatique, une expression régulière ou expression rationnelle ou expression normale ou motif, est une chaîne de caractères, qui décrit, selon une syntaxe précise, un ensemble de chaînes de caractères possibles. Les expressions régulières sont également appelées regex (de l'anglais regular expression). Elles sont issues des théories mathématiques des langages formels. Les expressions régulières sont aujourd’hui utilisées pour la lecture, le contrôle, la modification, et l'analyse de textes ainsi que la manipulation des langues formelles que sont les langages informatiques.

L'exemple d'expression régulière suivant permet de valider qu'une chaîne de caractère correspond à la syntaxe d'un nombre entier non signé, c'est à dire une suite non vide de chiffres :

[0-9]+

En détails :

Les crochets spécifient l'ensemble des caractères auquel doit appartenir le caractère courant de la chaîne. Dans cet exemple, l'ensemble est celui des chiffres de 0 à 9 inclus.
Le caractère plus indique de répéter le motif précédent au moins une fois (suite non vide).

MySQL utilise l'opérateur REGEXP ou RLIKE pour la validation d'une chaîne de caractères :

SELECT '123' REGEXP '^[0-9]+$';  -- 1

Les expressions rationnelles peuvent être analysées et testées via un débogueur en ligne comme https://regex101.com/.

Expressions rationnelles courantes
Caractère	Type	Explication
`.`	Point	N'importe quel caractère
`[...]`	crochets	classe de caractères : tous les caractères énumérés dans la classe, avec possibilité de plages dont les bornes sont séparées par "-". Ex : `[0-9a-z]` pour tout l'alphanumérique en minuscule, ou `[0-Z]` pour tous les caractères de la table Unicode entre "0" et "Z", c'est-à-dire l'alphanumérique majuscule plus ":;<=>?@"^[1].
`[^...]`	crochets et circonflexe	classe complémentée : tous les caractères sauf ceux énumérés.
`[...[...]]`	union	Union des deux ensembles
`[...&&[...]]`	intersection	Intersection des deux ensembles
`^`	circonflexe	Marque le début de la chaîne ou de la ligne.
`$`	dollar	Marque la fin de la chaîne ou de la ligne.
`\|`	barre verticale	Alternative - ou reconnaît l'un ou l'autre
`(...)`	parenthèses	groupe de capture : utilisé pour limiter la portée d'un masque ou de l'alternative, grouper un motif répété ou capturer une séquence
`\n`	référence	Même séquence que celle capturée précédemment par le n^ème groupe de capture
`\g{n}`	référence	Même séquence que celle capturée précédemment par le n^ème groupe de capture
`(?P<nom>pattern)`	Sous-motif nommé	Nomme le résultat d'un groupe de capture par un nom.
`\g{nom}`	référence	Même séquence que celle capturée précédemment par le groupe de capture nommé nom.
`\k<nom>`	référence	Même séquence que celle capturée précédemment par le groupe de capture nommé nom.

Par défaut, les caractères et groupes ne sont pas répétés. Les quantificateurs permettent de spécifier le nombre de répétitions et sont spécifiés immédiatement après le caractère ou groupe concerné.

Quantificateurs
Caractère	Type	Explication
`*`	astérisque	0, 1 ou plusieurs occurrences
`+`	plus	1 ou plusieurs occurrences
`?`	interrogation	0 ou 1 occurrence
`{...}`	accolades	nombre de répétitions : spécifie le nombre de répétitions du motif précédent (minimum et maximum). Avec la présence de la virgule, quand le minimum est absent la valeur par défaut est zéro, quand le maximum est absent la valeur pas défaut est l'infini. Sans virgule (un seul nombre) il s'agit du nombre exact (minimum et maximum ont la même valeur). Exemples : `a{2}` deux occurrences de "a", `a{1,10}` (sans espace) entre une et dix, `a{,10}` jusqu'à 10 fois (de 0 à 10), `a{3,}` au moins 3 fois (de 3 à l'infini).

Par défaut les quantificateurs ne recherchent pas forcément la plus longue séquence de répétition possible. Il est possible de les suffixer avec un caractère pour modifier leur comportement.

Modificateurs de quantificateurs
Caractère	Type	Explication
`?`	réticent	Le quantificateur qui précède recherchera la plus petite séquence possible.
`+`	possessif	Le quantificateur qui précède recherchera la plus grande séquence possible.

Remarques :

Les caractères de début et fin de chaîne (^ et $) ne fonctionnent pas dans [] où ils ont un autre rôle.
Les opérateurs * et + sont toujours avides, pour qu'ils laissent la priorité il faut leur apposer un ? à leur suite^[2].

Classes de caractères POSIX^[3]
Classe	Signification
`[[:alpha:]]`	n'importe quelle lettre
`[[:digit:]]`	n'importe quel chiffre
`[[:xdigit:]]`	caractères hexadécimaux
`[[:alnum:]]`	n'importe quelle lettre ou chiffre
`[[:space:]]`	n'importe quel espace blanc
`[[:punct:]]`	n'importe quel signe de ponctuation
`[[:lower:]]`	n'importe quelle lettre en minuscule
`[[:upper:]]`	n'importe quelle lettre capitale
`[[:blank:]]`	espace ou tabulation
`[[:graph:]]`	caractères affichables et imprimables
`[[:cntrl:]]`	caractères d'échappement
`[[:print:]]`	caractères imprimables exceptés ceux de contrôle

Expressions rationnelles Unicode^[4]
Expression	Signification
`\\`	Antislash
`\C`	Caractère spécial C non interprété : `[ ] { } ( ) ? * . : \ & - ^ $`
`\Q...\E`	Séquence littérale non interprétée
`\0xxx`	Caractère Unicode (1 à 3 chiffres octaux)
`\a`	Alarme (ASCII 07)
`\A`	Début de chaîne
`\b`	Caractère de début ou fin de mot
`\B`	Caractère qui n'est pas début ou fin de mot
`\cX`	Caractère de contrôle ASCII (X étant une lettre)
`\d`	Chiffre
`\D`	Non chiffre
`\e`	Escape (ASCII 1B)
`\f`	Form-feed (ASCII 0C)
`\G`	Fin de la correspondance précédente
`\h`	Espace blanc horizontal `[ \t\xA0\u1680\u180e\u2000-\u200a\u202f\u205f\u3000]`
`\H`	Non espace blanc horizontal `[^\h]`
`\n`	Fin de ligne
`\pL`, `\p{L}`, `\p{Letter}`	Lettre (dans tout langage)
`\r`	Retour charriot
`\R`	Retour à la ligne, équivaut à `\u000D\u000A\|[\u000A\u000B\u000C\u000D\u0085\u2028\u2029]`
`\s`	Caractères espace `[ \t\n\x0B\f\r]`
`\S`	Non caractères espace `[^\s]`
`\t`	Tabulation
`\uxxxx`	Caractère Unicode (4 chiffres hexadécimaux)
`\v`	Espace blanc vertical `[\n\x0B\f\r\x85\u2028\u2029]`
`\V`	Non espace blanc vertical `[^\v]`
`\w`	Caractère alphanumérique : lettre, chiffre ou underscore
`\W`	Caractère qui n'est pas lettre, chiffre ou underscore
`\xxx`	Caractère Unicode (2 chiffres hexadécimaux)
`\x{xx...x}`	Caractère Unicode (chiffres hexadécimaux)
`\X`	Caractère Unicode du groupe de graphèmes étendu
`\z`	Fin de chaîne

Constructeurs spéciaux : Ces fonctions précèdent l'expression à laquelle elles s'appliquent, et le tout doit être placé entre parenthèses.

?: : groupe non capturant. Ignorer le groupe de capture lors de la numérotation des backreferences. Exemple : ((?:sous-chaine_non_renvoyée|autre).*).
La présence d'un groupe capturant peut engendrer une allocation mémoire supplémentaire. Si une expression régulière particulièrement complexe provoque une erreur de mémoire, essayez de remplacer les groupes capturant non référencés et inutilisés par des groupes non-capturant en ajoutant ?: juste après la parenthèse ouvrante, et en décalant les numéros des groupes référencés.
?> : groupe non capturant indépendant.
?<= : positive lookbehind, vérifier (sans consommer) que ce qui précède correspond au motif spécifié. Exemple :
Chercher une lettre u précédée d'une lettre q : (?<=q)u
?<! : negative lookbehind, vérifier (sans consommer) que ce qui précède ne correspond pas au motif spécifié.
?= : positive lookahead, vérifier (sans consommer) que ce qui suit correspond au motif spécifié.
?! : negative lookahead, vérifier (sans consommer) que ce qui suit ne correspond pas au motif spécifié. Exemples :
Chercher une lettre q non suivie d'une lettre u : q(?!u)

((?!sous-chaine_exclue).)

<(?!body).*> : pour avoir toutes les balises HTML sauf "body".

début((?!mot_exclu).)*fin^[5] : pour rechercher tout ce qui ne contient pas un mot entre deux autres.

(?!000|666) : pour exclure 000 et 666^[6].

Options :

Les options d'interprétation sont en général spécifiées à part. Mais certaines API ne permettent pas de les spécifier. Il est possible d'insérer ces options dans l'expression régulière^[7].

(?optionsactivées-optionsdésactivées)

Exemples :

Chercher un mot composé de voyelles sans tenir compte de la casse :
(?i)[AEIOUY]+
Chercher un mot composé de voyelles en tenant compte de la casse, ici en majuscules :
(?-i)[AEIOUY]+

Les options s'appliquent à toute l'expression quelle que soit leur position dans l'expression.

Introduction

En MySQL 5.1, les expressions régulières fonctionnent sur des textes en octets et peuvent donc donner des résultats inattendus avec des textes en Unicode^[8].

MySQL propose plusieurs opérateurs et fonctions pour le regex^[9]. toutefois, c'est assez limité car en MySQL 8.0 il n'existe toujours pas de groupe de capture.

REGEXP

Syntaxe

SELECT 'string' REGEXP 'pattern'

RLIKE est synonyme de REGEXP.

Dans MySQL, le double antislash fait office de caractère d'échappement.

Exemples

Est-ce que la sélection est différente des lettres de A à Z :

SELECT 'a' REGEXP '^[a-z]';  -- 1
SELECT 'A' REGEXP '^[a-z]';  -- 1
SELECT '1' REGEXP '^[a-z]';  -- 0
SELECT 'é' REGEXP '^[a-z]';  -- 0

Sélectionner les pages de wiki qui matchent un pattern :

USE frwiki_p;
SELECT p.page_title
FROM page p
JOIN page_props pp ON p.page_id = pp.pp_page
WHERE p.page_namespace = 0 AND pp.pp_page REGEXP '\n *titre *='

REGEXP_LIKE

Même rôle que REGEXP mais on peut définir les options en argument. Ex :

SELECT REGEXP_LIKE('A', '^[a-z]', 'i'); -- 1
SELECT REGEXP_LIKE('A', '^[a-z]', 'c'); -- 0

REGEXP_REPLACE

Fonctionne comme la fonction REPLACE() mais avec du regex. Exemple pour retirer les caractères spéciaux d'une chaine :

select REGEXP_REPLACE('hello+$ù^:world', '[^a-z]', '');
-- helloworld

REGEXP_SUBSTR

Extrait une sous-chaine (substring). Ex :

SELECT REGEXP_REPLACE(REGEXP_REPLACE('<html><title>Page 1</title>...</html>', '.*<title>', ''), '</title>.*', '');
-- Page 1

Références

↑ https://unicode-table.com/fr/
↑ https://docstore.mik.ua/orelly/webprog/pcook/ch13_05.htm
↑ https://www.regular-expressions.info/posixbrackets.html
↑ https://www.regular-expressions.info/unicode.html
↑ https://www.regextester.com/15
↑ Jan Goyvaerts, Steven Levithan, Regular Expressions Cookbook, O'Reilly Media, Inc., 22 mai 2009 (lire en ligne)
↑ Les options sont appelées modificateurs (modifiers en anglais), voir https://www.regular-expressions.info/modifiers.html
↑ Pour se familiariser avec Unicode, on peut lire À la découverte d'Unicode
↑ https://dev.mysql.com/doc/refman/8.0/en/regexp.html

[1] ttps://unicode-table.com/fr/

[2] ttps://docstore.mik.ua/orelly/webprog/pcook/ch13_05.htm

[3] ttps://www.regular-expressions.info/posixbrackets.html

[4] ttps://www.regular-expressions.info/unicode.html

[5] ttps://www.regextester.com/15

[6] Jan Goyvaerts, Steven Levithan, Regular Expressions Cookbook, O'Reilly Media, Inc., 22 mai 2009 (lire en ligne)

[7] Les options sont appelées modificateurs (modifiers en anglais), voir https://www.regular-expressions.info/modifiers.html

[8] Pour se familiariser avec Unicode, on peut lire À la découverte d'Unicode

[9] ttps://dev.mysql.com/doc/refman/8.0/en/regexp.html

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]