REGEXP_REPLACE no SQL: limpeza de strings com regex e os flags g e i

REGEXP_REPLACE encontra em uma string cada substring que casa com uma expressao regular e a troca por um texto de substituicao. E a ferramenta de referencia para limpar dados sujos dentro da propria consulta: tirar lixo de um telefone, colapsar espacos ou reformatar nomes sem exportar para o codigo da aplicacao.

Assinatura e uma substituicao basica

No PostgreSQL a funcao e REGEXP_REPLACE(source, pattern, replacement [, flags]). Por padrao ela substitui apenas a primeira correspondencia, entao quase sempre voce vai querer o flag 'g' (global).

SELECT
  REGEXP_REPLACE('+1 (555) 123-45-67', '[^0-9]', '', 'g') AS digits,
  REGEXP_REPLACE('hello   world', '\s+', ' ', 'g')        AS one_space;
-- 15551234567 | hello world

Pontos principais:

• Sem 'g' so a primeira correspondencia e trocada: REGEXP_REPLACE('a-b-c', '-', '+') da a+b-c.
• A classe [^0-9] significa "qualquer caractere que nao seja um digito", a forma classica de manter so os digitos.
• Uma string de substituicao vazia '' simplesmente apaga as correspondencias.

Os flags g, i e multilinha

Os flags sao passados como string no quarto argumento e se combinam: 'gi' e global e sem diferenciar maiusculas ao mesmo tempo.

SELECT
  REGEXP_REPLACE(email, 'GMAIL', 'gmail', 'gi') AS norm,
  REGEXP_REPLACE(name, '\s+', ' ', 'g')         AS clean_name
FROM users;

• g - substitui todas as ocorrencias, nao apenas a primeira.
• i - sem diferenciar maiusculas, entao Gmail, GMAIL e gmail casam igual.
• n (ou m) - modo multilinha, onde ^ e $ passam a ancorar nas quebras de linha internas.

Retroreferencias na substituicao

Os parenteses (...) no padrao capturam trechos em grupos, e a string de substituicao os referencia como \1, \2, e assim por diante. Isso permite reordenar e reformatar partes de uma string.

SELECT
  REGEXP_REPLACE(name, '^(\w+)\s+(\w+)$', '\2, \1') AS last_first
FROM employees;
-- 'Ada Lovelace' -> 'Lovelace, Ada'

Outro exemplo: normalizar um telefone para um unico formato extraindo grupos de digitos:

SELECT
  REGEXP_REPLACE('5551234567', '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS pretty;
-- (555) 123-4567

A retroreferencia \1 aponta para o texto capturado pelo primeiro grupo, entao a ordem dos parenteses importa.

POSIX versus PCRE: a grande pegadinha

O PostgreSQL usa o dialeto POSIX ARE, enquanto o MySQL e muitas linguagens usam PCRE. Eles se parecem, mas os detalhes mordem.

• No POSIX (PostgreSQL) use [[:alnum:]] para alfanumericos, ou os atalhos \w, \d, \s. O \b (limite de palavra) ao estilo Perl existe, mas o comportamento nas bordas difere.
• Quantificadores preguicosos *? e +? sao suportados no PostgreSQL, mas grupos nomeados e lookbehind nao.
• O erro mais comum: esquecer o 'g' e se perguntar por que so uma ocorrencia mudou.

-- GOTCHA: without 'g' only the first space collapses
SELECT REGEXP_REPLACE('a  b  c', '\s+', '_');     -- a_b  c
SELECT REGEXP_REPLACE('a  b  c', '\s+', '_', 'g'); -- a_b_c

Diferencas no MySQL e ClickHouse

A funcao nao e universal e se comporta de forma diferente entre os motores.

• MySQL 8+ tem REGEXP_REPLACE(source, pattern, replacement [, pos, occurrence, match_type]). Nao ha string de flags como no Postgres: a substituicao global e o padrao (quando occurrence = 0), enquanto maiusculas e multilinha vem do match_type como 'i' ou 'm'. As retroreferencias sao escritas como $1, nao \1.

SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits FROM users;
SELECT REGEXP_REPLACE(name, '^(\\w+) (\\w+)$', '$2, $1') AS last_first FROM employees;

• ClickHouse separa isso em replaceRegexpOne (primeira correspondencia) e replaceRegexpAll (todas), e as referencias a grupos sao escritas como \1.

SELECT replaceRegexpAll(phone, '[^0-9]', '') AS digits FROM users;

Lembre-se: a essencia e a mesma, um padrao, uma substituicao e um flag global. So mudam o nome da funcao, a sintaxe dos flags e o estilo de retroreferencia (\1 versus $1).