Menu

LIKE e ILIKE in PostgreSQL ed espressioni regolari

LIKE confronta il testo con un pattern che usa % e _, ILIKE fa lo stesso ignorando maiuscole e minuscole, e gli operatori ~ confrontano espressioni regolari POSIX.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

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:

FunzioneRestituisce
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.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia