Expressões regulares em bancos de dados SQL

Escrito por Aurelio Jargas em 21/12/2014

Você sabia que é possível usar as poderosas expressões regulares em suas queries SQL no banco de dados?

O operador LIKE até quebra um galho para pesquisas mais simples, com seus metacaracteres % e _. Mas para ir além e fazer pesquisas realmente complexas, você precisará de toda a gama de metacaracteres das expressões regulares.

As expressões não fazem parte do padrão SQL (ainda), mas alguns bancos de dados já se adiantaram e passaram a suportá-las. Porém, pela falta do padrão, cada banco implementou de seu próprio jeito, o que gerou uma diferença na sintaxe de uso. Nos exemplos seguintes, serão mostradas as sintaxes dos principais bancos.

Suponha que temos um banco de dados com uma tabela chamada veiculos, que traz vários dados sobre cada veículo. A placa do veículo, por exemplo, deve estar no formato AAA-9999. Este é um formato simples, que conseguimos representar com a expressão regular ^[A-Z]{3}-\d{4}$. Para listar todos os veículos cujas placas estão no formato correto:

-- Listar os veículos cujas placas estão no formato AAA-9999

-- MySQL, MariaDB, SQLite
SELECT * FROM veiculos WHERE placa REGEXP '^[A-Z]{3}-\d{4}$';

-- PostgreSQL
SELECT * FROM veiculos WHERE placa ~ '^[A-Z]{3}-\d{4}$';

-- Oracle
SELECT * FROM veiculos WHERE REGEXP_LIKE(placa, '^[A-Z]{3}-\d{4}$');

As coisas ainda estão um pouco bagunçadas, não? Enquanto o SQLite e o MySQL usam o operador REGEXP, o PostgreSQL usa o operador ~ e o Oracle usa uma função REGEXP_LIKE(). Mas tudo bem, use essa colinha para lembrar.

Para fazer uma expressão negada, basta colocar NOT antes do operador, ou no caso do PostgreSQL, usar !~. Voltando ao exemplo anterior, agora buscando os veículos cujas placas não seguem o padrão:

-- Listar os veículos cujas placas NÃO estão no formato AAA-9999

-- MySQL, MariaDB, SQLite
SELECT * FROM veiculos WHERE placa NOT REGEXP '^[A-Z]{3}-\d{4}$';

-- PostgreSQL
SELECT * FROM veiculos WHERE placa !~ '^[A-Z]{3}-\d{4}$';

-- Oracle
SELECT * FROM veiculos WHERE NOT REGEXP_LIKE(placa, '^[A-Z]{3}-\d{4}$');

E aí, já está tendo ideias?

E que tal se eu te contar que além de pesquisas, você também pode fazer alterações usando expressões regulares? Aí sim, o negócio começa a ficar muito interessante!

O exemplo anterior listou todas as placas que estão fora do padrão AAA-9999. Lembre-se, o usuário é criativo na hora de digitar dados, então é comum encontrarmos placas em formatos variados, como AAA 9999, AAA.9999, AAA:9999, AAA9999, …

Em geral, é a aplicação quem faz a limpeza destes dados na hora de mostrá-los na tela, removendo os caracteres indesejados e formatando no padrão correto. Mas você já pode fazer isso na própria consulta SQL, usando substituição de texto com expressões regulares:

-- Mostra o valor original e o formatado como AAA-9999

-- MariaDB
SELECT
  placa AS placa_original,
  REGEXP_REPLACE(placa, '^([A-Z]{3})[.: ]?(\d{4})$', '\\1-\\2') AS placa_ok
FROM veiculos;

-- PostgreSQL
SELECT
  placa AS placa_original,
  regexp_replace(placa, '^([A-Z]{3})[.: ]?(\d{4})$', '\\1-\\2') AS placa_ok
FROM veiculos;

-- Oracle
SELECT
  placa AS placa_original,
  REGEXP_REPLACE(placa, '^([A-Z]{3})[.: ]?(\d{4})$', '\1-\2') AS placa_ok
FROM veiculos;

Que tal ir um passo além, e já corrigir estes dados diretamente no banco de uma vez? Assim, eles já estarão sempre corretos e ninguém mais precisará se preocupar em formatá-los posteriormente.

Primeiro, você aplica a técnica do exemplo anterior, de usar o SELECT para mostrar o valor original e o novo, e vai com calma, ajustando sua expressão regular até ela ficar perfeita, formatando corretamente 100% dos casos. Depois, você utiliza a mesma expressão no UPDATE, corrigindo de uma vez todos os dados:

-- Corrigir todas as placas para o formato AAA-9999

-- MariaDB
UPDATE veiculos
SET placa = REGEXP_REPLACE(placa, '^([A-Z]{3})[.: ]?(\d{4})$', '\\1-\\2');

-- PostgreSQL
UPDATE veiculos
SET placa = regexp_replace(placa, '^([A-Z]{3})[.: ]?(\d{4})$', '\\1-\\2');

-- Oracle
UPDATE veiculos
SET placa = REGEXP_REPLACE(placa, '^([A-Z]{3})[.: ]?(\d{4})$', '\1-\2');

Se você também já perdeu muito tempo de vida fazendo faxina nos dados do banco, sabe que poder usar expressões regulares nestes casos NÃO TEM PREÇO.

E assim amiguinhos, vamos aos poucos resolvendo os problemas causados por pessoas toscas, sistemas toscos e migrações toscas :)

Se você quiser saber mais sobre expressões regulares e como usá-las em bancos de dados, leia meu livro Expressões Regulares - Uma abordagem divertida.