9693 shaares
15 liens privés
15 liens privés
PostgreSQL — Cheat sheet Regex
Le moteur regex de PostgreSQL est ARE (Advanced Regular Expressions, bibliothèque de
Henry Spencer, proche de la syntaxe Tcl). Ce n'est pas du PCRE : pas de \b, pas de
\p{...}, pas de \K.
⚠️ Pièges classiques PCRE → PostgreSQL
| PCRE / autres moteurs | PostgreSQL ARE |
|---|---|
\b (limite de mot) |
\y |
| début de mot | \m |
| fin de mot | \M |
\B (pas une limite) |
\Y |
\b |
⚠️ backspace (caractère de contrôle) |
\p{L} (propriété Unicode) |
non supporté → [[:alpha:]], \w |
Opérateurs de correspondance
'chaine' ~ 'motif' -- correspondance (sensible à la casse)
'chaine' ~* 'motif' -- correspondance (insensible à la casse)
'chaine' !~ 'motif' -- PAS de correspondance
'chaine' !~* 'motif' -- PAS de correspondance (insensible à la casse)SELECT 'PostgreSQL 16' ~ '\d+'; -- true
SELECT 'test' ~* '^TEST$'; -- true
SELECT 'mot test' ~ '\ytest\y'; -- true (limite de mot)Fonctions principales
| Fonction | Rôle | Exemple |
|---|---|---|
regexp_replace(str, motif, remp [, début [, flags]]) |
Remplacer (1re occurrence, ou toutes avec g) |
regexp_replace('a1 b2', '\d', 'X') → aX b2 |
regexp_match(str, motif) |
1re correspondance → text[] |
regexp_match('a1 b2', '(\d)') → {1} |
regexp_matches(str, motif [, flags]) |
Toutes les correspondances (avec flag g) → setof text[] |
regexp_matches('a1 b2', '\d', 'g') → {1}, {2} |
regexp_split_to_array(str, motif) |
Découpage → tableau | regexp_split_to_array('a,b;c', '[,;]') → {a,b,c} |
regexp_split_to_table(str, motif) |
Découpage → lignes | idem, une ligne par élément |
substring(str FROM motif) |
Extraire selon le motif | substring('a123b' from '\d+') → 123 |
regexp_like(str, motif [, flags]) (PG ≥ 15) |
Booléen, équivalent ~ |
regexp_like('abc', 'B', 'i') → t |
regexp_count(str, motif) (PG ≥ 15) |
Nombre d'occurrences | regexp_count('a1b2', '\d') → 2 |
regexp_substr(str, motif) (PG ≥ 15) |
Extrait la sous-chaîne | regexp_substr('a123b', '\d+') → 123 |
regexp_instr(str, motif) (PG ≥ 15) |
Position de l'occurrence | regexp_instr('abc12', '\d') → 4 |
Remplacement avec références de groupes : \1, \2…
SELECT regexp_replace('Dupont Jean', '^(\w+) (\w+)$', '\2 \1');
-- Jean Dupont
SELECT regexp_replace('a1 b2 c3', '\d', 'X', 'g'); -- g = toutes les occurrences
-- aX bX cXFlags des fonctions (dernier argument, chaîne text)
| Flag | Effet |
|---|---|
i |
Insensible à la casse |
g |
Toutes les occurrences (remplacement / matches) |
n ou m |
Sensible aux sauts de ligne : . matche \n, ^/$ s'ancrent sur chaque ligne |
p |
Sensibilité partielle aux sauts de ligne (. matche \n, mais ^/$ restent aux extrémités) |
w |
Sensibilité complète (^/$ uniquement aux sauts de ligne) |
Flags embarqués dans le motif (syntaxe ARE)
'ABC' ~ '(?i)abc' -- true : (?i) = insensible à la casse à partir de ce point
'ABC' ~ '(?i\:abc)' -- true : limités à un groupe
'a b \d' ~ '(?x) a b \d' -- x : mode étendu (espaces et #commentaires ignorés)
'aaa' ~ '(?s)a+?' -- s : quantificateurs non-gloutons ; g : gloutonsClasses et ensembles
| Motif | Signification |
|---|---|
\d / \D |
chiffre / non-chiffre |
\w / \W |
caractère de mot (alphanumérique + _) / complément |
\s / \S |
espace / non-espace |
[[:alpha:]] [[:digit:]] [[:alnum:]] |
classes POSIX |
[[:upper:]] [[:lower:]] [[:space:]] |
casse, espaces |
[[:punct:]] [[:xdigit:]] [[:cntrl:]] |
ponctuation, hexa, contrôle |
[[:blank:]] [[:print:]] [[:graph:]] |
espace/tab, imprimable, visible |
⚠️ Préférer [[:digit:]] ou [0-9] à \d dans un crochet.
SELECT 'a1' ~ '^\w\d$'; -- true
SELECT 'x' ~ '[[\:alpha:]]'; -- trueAncres et contraintes
| Motif | Signification |
|---|---|
^ / $ |
début / fin de la chaîne (ou de ligne avec flag n) |
\A / \Z |
début / fin de la chaîne uniquement (jamais affectés par n) |
\y |
limite de mot (l'équivalent PCRE de \b) |
\m / \M |
début / fin de mot |
\Y |
position qui N'est PAS une limite de mot |
Quantificateurs
| Motif | Signification |
|---|---|
* + ? |
0+ / 1+ / 0 ou 1 — gloutons par défaut |
{m} {m,} {,n} {m,n} |
répétitions exactes / bornes |
*? +? ?? {m,n}? |
versions non gloutonnes (paresseuses) |
Groupes et assertions
| Motif | Signification |
|---|---|
(...) |
groupe capturant |
(?:...) |
groupe non capturant |
\1 … \9 |
rétro-références dans le motif |
(?=...) / (?!...) |
lookahead positif / négatif |
(?<=...) / (?<!...) |
lookbehind positif / négatif |
SELECT regexp_matches('abab', '(ab)\1'); -- {ab} : rétro-référence
SELECT regexp_matches('foo bar', '\w+(?= bar)'); -- {foo} : lookaheadÉchappements de caractères
\n \t \r \f \v \a \b⚠️ (backspace) — et \. \* \( … pour les
métacaractères littéraux.
Écriture des littéraux SQL
- Avec
standard_conforming_strings = on(défaut depuis PG 9.1) :'\ytest\y'
fonctionne tel quel, un seul antislash. - Avec les chaînes
E'...': doubler les antislashs →E'\\ytest\\y'. - Échapper une apostrophe SQL :
'\d{2}''\d{3}'ou dollar-quoting$$\d{2}$$.
SIMILAR TO (SQL standard, à connaître)
'abc' SIMILAR TO 'a%' -- % = n'importe quelle suite, _ = un caractère
'abc' SIMILAR TO '(a|b)%' -- alternation avec |, équivalent regex + ancrage total
'abc' NOT SIMILAR TO 'b%'Traduit en interne en regex ARE et ancré aux deux extrémités — en général, on préfère
directement ~ et ses variantes.
Recettes courantes
-- Valider un email (simple)
SELECT 'user@example.com' ~ '^[[\:alnum:]_.-]+@[[\:alnum:]-]+\.[[\:alpha:]]{2,}$';
-- Extraire le code postal
SELECT substring('75001 Paris' FROM '\d{5}');
-- Normaliser un numéro de téléphone
SELECT regexp_replace('06 12 34 56 78', '\D', '', 'g'); -- 0612345678
-- Découper sur plusieurs séparateurs
SELECT regexp_split_to_table('a,b;c d', '[,; ]');
-- Lignes où un mot apparaît exactement (pas comme sous-mot)
SELECT * FROM t WHERE libelle ~ '\ychaud\y';
-- Dédupliquer un mot immédiatement répété
SELECT regexp_replace('bon bonjour', '(\w+) \1', '\1'); -- bonjour