LIKE compare du texte à un motif dans lequel % correspond à n'importe quelle suite de caractères et _ à exactement un caractère, donc email LIKE '%@gmail.com' trouve les adresses Gmail. LIKE est sensible à la casse. ILIKE fait la même comparaison sans tenir compte de la casse, et ~ et ~* testent des expressions régulières POSIX.
La requête renvoie Asha et Bella : ILIKE ignore les majuscules de l'adresse de Bella, et le .co de Chen ne correspond pas à la fin du motif.
Les motifs avec % et _
Toute la chaîne doit correspondre au motif, donc un motif sans % se comporte comme =. NOT LIKE et NOT ILIKE inversent le test.
A-___ correspond à A-100 mais pas à A-2000, car chaque _ remplace exactement un caractère. Le a-300 en minuscule ne passe que le test ILIKE.
Chercher un % ou un _ littéral
Une barre oblique inverse échappe le caractère suivant dans un motif, donc '%50\%%' trouve le texte qui contient 50%. Quand les données contiennent elles-mêmes des barres obliques inverses, choisissez un autre caractère d'échappement avec ESCAPE :
SAVE\_% ne correspond qu'à SAVE_10. Sans l'échappement, _ correspondrait aussi au 1 de SAVE10.
Les expressions régulières avec ~
Les opérateurs regex sont ~ (correspond), ~* (correspond sans tenir compte de la casse), !~ et !~* (ne correspond pas). Une regex peut correspondre n'importe où dans la chaîne, sauf si vous l'ancrez avec ^ et $ :
Seul 555-0101 correspond au format avec tiret. regexp_replace avec l'option g supprime tout ce qui n'est pas un chiffre, donc (555) 0199 devient 5550199 et call me devient une chaîne vide.
Extraire et compter les correspondances
D'autres fonctions regex renvoient ce qui a correspondu au lieu de vrai ou faux :
| Fonction | Renvoie |
|---|---|
substring(s from 'pattern') | La première correspondance, ou le premier groupe entre parenthèses |
regexp_match(s, 'pattern') | Les groupes de la première correspondance, en text[] |
regexp_matches(s, 'pattern', 'g') | Une ligne par correspondance |
regexp_count(s, 'pattern') | Le nombre de correspondances (depuis PostgreSQL 15) |
regexp_substr(s, 'pattern') | La première correspondance (depuis PostgreSQL 15) |
regexp_like(s, 'pattern') | Comme ~, sous forme de fonction (depuis PostgreSQL 15) |
regexp_split_to_table(s, 'pattern') | Une ligne par morceau entre les correspondances |
substring('Order 4521 shipped' from '\d+') renvoie 4521. Le chapitre sur la recherche de motifs du manuel liste les options et la syntaxe des regex.
Faire utiliser un index aux recherches par motif
Un index B-tree peut servir un motif ancré au début, comme LIKE 'abc%', quand la base utilise la collation C ou que l'index est construit avec text_pattern_ops :
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
Un motif qui commence par %, ILIKE et la plupart des regex ne peuvent pas utiliser un B-tree. Pour ceux-là, l'extension pg_trgm ajoute des index trigrammes qui servent LIKE, ILIKE, ~ et ~* :
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
Pour une égalité insensible à la casse plutôt qu'un motif, un index sur lower(email), interrogé avec lower(email) = 'ben@work.io', est le choix le plus simple. La page des fonctions de chaîne présente lower, position et split_part.
Questions fréquentes
Comment vérifier qu'une chaîne contient une sous-chaîne en PostgreSQL ?
Utilisez col LIKE '%text%', ou col ILIKE '%text%' pour ignorer la casse. position('text' in col) > 0 et strpos(col, 'text') > 0 marchent aussi et n'obligent pas à échapper % ni _.
LIKE est-il sensible à la casse en PostgreSQL ?
Oui. 'Apple' LIKE 'a%' est faux. Utilisez ILIKE, comparez lower(col) LIKE 'a%', ou prenez l'opérateur regex insensible à la casse ~*.
Comment tester plusieurs motifs à la fois ?
Combinez LIKE ANY avec un tableau : name LIKE ANY (ARRAY['a%', 'b%']) est vrai quand l'un des motifs correspond, et LIKE ALL les exige tous. Une alternative en regex comme name ~ '^(a|b)' fait la même chose.
Qu'est-ce que SIMILAR TO en PostgreSQL ?
Un mélange standard SQL de LIKE et d'expressions régulières : il utilise % et _ plus |, *, + et des groupes, et doit correspondre à toute la chaîne. LIKE ou les opérateurs ~ sont en général plus clairs.