LIKE confronta il testo con un pattern in cui % corrisponde a qualsiasi sequenza di caratteri e _ a esattamente uno, quindi email LIKE '%@gmail.com' trova gli indirizzi Gmail. LIKE distingue tra maiuscole e minuscole. ILIKE è lo stesso confronto senza questa distinzione, e ~ e ~* confrontano espressioni regolari POSIX.
Restituisce Asha e Bella: ILIKE ignora le maiuscole nell'indirizzo di Bella, e il .co di Chen non corrisponde alla parte finale.
Confrontare con % e _
L'intera stringa deve corrispondere al pattern, quindi un pattern senza % si comporta come =. NOT LIKE e NOT ILIKE invertono il confronto.
A-___ corrisponde a A-100 ma non a A-2000, perché ogni _ sta per esattamente un carattere. Il minuscolo a-300 passa solo il test con ILIKE.
Cercare un % o un _ letterale
Un backslash fa l'escape del carattere successivo in un pattern, quindi '%50\%%' trova il testo che contiene 50%. Quando i backslash fanno parte dei dati, scegli un altro carattere di escape con ESCAPE:
SAVE\_% corrisponde solo a SAVE_10. Senza l'escape, _ corrisponderebbe anche all'1 di SAVE10.
Espressioni regolari con ~
Gli operatori regex sono ~ (corrisponde), ~* (corrisponde, ignorando maiuscole e minuscole), !~ e !~* (non corrisponde). Una regex trova corrispondenze in qualsiasi punto della stringa, a meno che tu non la ancori con ^ e $:
Solo 555-0101 corrisponde al formato con il trattino. regexp_replace con il flag g rimuove ogni carattere che non è una cifra, quindi (555) 0199 diventa 5550199 e call me diventa una stringa vuota.
Estrarre e contare le corrispondenze
Altre funzioni regex restituiscono ciò che ha corrisposto invece di vero o falso:
| Funzione | Restituisce |
|---|---|
substring(s from 'pattern') | La prima corrispondenza, o il primo gruppo tra parentesi |
regexp_match(s, 'pattern') | I gruppi della prima corrispondenza come text[] |
regexp_matches(s, 'pattern', 'g') | Una riga per corrispondenza |
regexp_count(s, 'pattern') | Il numero di corrispondenze (da PostgreSQL 15) |
regexp_substr(s, 'pattern') | La prima corrispondenza (da PostgreSQL 15) |
regexp_like(s, 'pattern') | Come ~, ma in forma di funzione (da PostgreSQL 15) |
regexp_split_to_table(s, 'pattern') | Una riga per ogni pezzo tra le corrispondenze |
substring('Order 4521 shipped' from '\d+') restituisce 4521. Il capitolo sul pattern matching del manuale elenca i flag e la sintassi delle regex.
Far usare un indice alle ricerche per pattern
Un indice B-tree può servire un pattern ancorato all'inizio, come LIKE 'abc%', quando il database usa la collation C o l'indice è creato con text_pattern_ops:
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
Un pattern che inizia con %, ILIKE e la maggior parte delle regex non possono usare un B-tree. Per questi casi, l'estensione pg_trgm aggiunge indici a trigrammi che servono LIKE, ILIKE, ~ e ~*:
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
Per un'uguaglianza senza distinzione tra maiuscole e minuscole, invece che per i pattern, un indice su lower(email), interrogato con lower(email) = 'ben@work.io', è la scelta più semplice. La pagina sulle funzioni per le stringhe copre lower, position e split_part.
Domande frequenti
Come controllo se una stringa contiene una sottostringa in PostgreSQL?
Usa col LIKE '%text%', oppure col ILIKE '%text%' per ignorare maiuscole e minuscole. Funzionano anche position('text' in col) > 0 e strpos(col, 'text') > 0, che non richiedono l'escape di % o _.
LIKE distingue tra maiuscole e minuscole in PostgreSQL?
Sì. 'Apple' LIKE 'a%' è falso. Usa ILIKE, confronta lower(col) LIKE 'a%', oppure usa l'operatore regex senza distinzione tra maiuscole e minuscole ~*.
Come confronto più pattern alla volta?
Combina LIKE ANY con un array: name LIKE ANY (ARRAY['a%', 'b%']) è vero quando corrisponde almeno un pattern, e LIKE ALL li richiede tutti. Un'alternativa regex come name ~ '^(a|b)' fa lo stesso.
Cos'è SIMILAR TO in PostgreSQL?
Un misto, previsto dallo standard SQL, tra LIKE ed espressioni regolari: usa % e _ più |, *, + e i gruppi, e deve corrispondere all'intera stringa. LIKE o gli operatori ~ di solito sono più chiari.