Menu

LIKE, ILIKE e expressões regulares no PostgreSQL

LIKE compara texto com um padrão usando % e _, ILIKE faz o mesmo ignorando maiúsculas, e os operadores ~ aplicam expressões regulares POSIX.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

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çãoRetorna
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.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar