LIKE compara texto com um padrão em que % corresponde a qualquer sequência de caracteres e _ a exatamente um, então email LIKE '%@gmail.com' encontra endereços do Gmail. LIKE diferencia maiúsculas. ILIKE faz a mesma comparação ignorando maiúsculas, e ~ e ~* aplicam expressões regulares POSIX.
O resultado traz Asha e Bella: ILIKE ignora as maiúsculas no endereço da Bella, e o .co do Chen não bate com o final.
Padrões com % e _
O padrão precisa bater com o texto inteiro, então um padrão sem % funciona como =. NOT LIKE e NOT ILIKE invertem o resultado.
A-___ bate com A-100 mas não com A-2000, porque cada _ representa exatamente um caractere. O a-300 em minúsculas só passa no teste do ILIKE.
Buscar um % ou _ literal
Uma barra invertida escapa o próximo caractere do padrão, então '%50\%%' encontra textos que contêm 50%. Quando os dados têm barras invertidas, escolha outro caractere de escape com ESCAPE:
SAVE\_% só bate com SAVE_10. Sem o escape, _ também bateria com o 1 de SAVE10.
Expressões regulares com ~
Os operadores de regex são ~ (bate), ~* (bate ignorando maiúsculas), !~ e !~* (não bate). Uma regex bate em qualquer ponto do texto, a menos que você a ancore com ^ e $:
Só 555-0101 está no formato com hífen. regexp_replace com a flag g remove tudo que não é dígito, então (555) 0199 vira 5550199 e call me vira um texto vazio.
Extrair e contar correspondências
Outras funções de regex devolvem o trecho encontrado em vez de verdadeiro ou falso:
| Função | Retorna |
|---|---|
substring(s from 'pattern') | A primeira correspondência, ou o primeiro grupo entre parênteses |
regexp_match(s, 'pattern') | Os grupos da primeira correspondência como text[] |
regexp_matches(s, 'pattern', 'g') | Uma linha por correspondência |
regexp_count(s, 'pattern') | O número de correspondências (desde o PostgreSQL 15) |
regexp_substr(s, 'pattern') | A primeira correspondência (desde o PostgreSQL 15) |
regexp_like(s, 'pattern') | O mesmo que ~, como função (desde o PostgreSQL 15) |
regexp_split_to_table(s, 'pattern') | Uma linha por trecho entre as correspondências |
substring('Order 4521 shipped' from '\d+') retorna 4521. O capítulo de pattern matching do manual lista as flags e a sintaxe de regex.
Fazer buscas por padrão usarem um índice
Um índice B-tree atende um padrão ancorado no início, como LIKE 'abc%', quando o banco usa a collation C ou o índice é criado com text_pattern_ops:
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
Um padrão que começa com %, o ILIKE e a maioria das regex não conseguem usar um B-tree. Para esses casos, a extensão pg_trgm adiciona índices de trigramas que atendem LIKE, ILIKE, ~ e ~*:
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
Para igualdade ignorando maiúsculas, sem padrão, a opção mais simples é um índice em lower(email), consultado com lower(email) = 'ben@work.io'. A página de funções de texto explica lower, position e split_part.
Perguntas frequentes
Como verificar se um texto contém uma substring no PostgreSQL?
Use col LIKE '%text%', ou col ILIKE '%text%' para ignorar maiúsculas. position('text' in col) > 0 e strpos(col, 'text') > 0 também funcionam e não exigem escapar % nem _.
O LIKE diferencia maiúsculas no PostgreSQL?
Sim. 'Apple' LIKE 'a%' é falso. Use ILIKE, compare lower(col) LIKE 'a%' ou use o operador de regex que ignora maiúsculas, ~*.
Como buscar vários padrões de uma vez?
Combine LIKE ANY com um array: name LIKE ANY (ARRAY['a%', 'b%']) é verdadeiro quando qualquer padrão bate, e LIKE ALL exige todos. Uma alternância de regex como name ~ '^(a|b)' faz o mesmo.
O que é SIMILAR TO no PostgreSQL?
Uma mistura de LIKE com expressões regulares do padrão SQL: usa % e _ mais |, *, + e grupos, e precisa bater com o texto inteiro. LIKE ou os operadores ~ costumam ser mais claros.