Oracle: Regexp

Fala pessoal, como vão?

Vocês já sentiram a necessidade de usar regex para resolver alguma regra de negócio diretamente no Oracle sem ter a necessidade de tratar isso no Java? Eu frequentemente tenho esta necessidade. Muitas vezes carregar o dado para a camada Java apenas para fazer um tratamento em uma coluna pode ser bem custoso e trabalhoso.

Contudo o Oracle nos fornece um conjunto de funções muito úteis para trabalhar com regex, são elas:

  • REGEXP_COUNT;
  • REGEXP_REPLACE;
  • REGEXP_LIKE;
  • REGEXP_SUBSTR;
  • REGEXP_INSTR;

Destas, as que mais uso são REGEXP_COUNT e REGEXP_REPLACE. Vou fazer um resumo de cada uma delas e dar um exemplo de uso.

REGEXP_COUNT

É usada para contar quantas vezes uma determinada expressão é encontrada no texto. Seu retorno é um number. Quando o retorno é 0 (zero), isto quer dizer que não encontrou a expressão procurada.

Aqui no projeto, muitas tabelas tem uma coluna para registrar críticas no registro, ou seja, avisa ao usuário que o registro tem que ser corrigido. Estas críticas são separada por | (pipe), e em alguns momentos preciso mostrar para o usuário a quantidade de críticas que o registro possui.

Aplicando a função REGEXP_COUNT, fica muito simples mostrar esta informação:

retorna a quantidade de críticas do tipo 'E'
select regexp_count(criticas_erro, 'E\..*?\|') from tabela;
view raw regexp_count.sql hosted with ❤ by GitHub

REGEXP_REPLACE

É usada para realizar substituições, adições e remoções de caracteres ou textos não desejado do texto original, esta função é análoga ao método String.replaceAll() do Java, que com certeza você já deve ter usado uma vez na vida… rsrsrs! Ela espera receber três argumentos: texto original, regex procurado e texto de substituição. Vide exemplo:

SUBSTITUI "_" POR " ", RETORNANDO "Eder Leite"
SELECT REGEXP_REPLACE('Eder_Leite', '_', ' ') FROM DUAL;
ADICIONA UM " " APÓS CADA UMA DOS CARACTERES DO TEXTO ORIGINAL, RETORNANDO "E d e r _ L e i t e "
OBSERVAÇÃO: AO USAR "\1", ESTAMOS DIZENDO QUE QUEREMOS QUE PRIMEIRO GRUPO DO FILTRO SEJA ADICIONADO,
O GRUPO É SEMPRE REPRESENTADO POR UM REGEX DELIMITADO POR PARENTESES.
SELECT REGEXP_REPLACE('Eder_Leite', '(.)', '\1 ') FROM DUAL;
REMOVENDO CARACTERES ESPECIAS DO TEXTO ORIGINAL, RETORNANDO "NA"
SELECT REGEXP_REPLACE("Não", '[^A-Za-z0-9]+', '') FROM DUAL;
view raw regexp_replace1.sql hosted with ❤ by GitHub

REGEXP_LIKE

Também é bastante útil, pois é capaz de realizar um filtro tipo LIKE do SQL mas usando regex para isso. Esta função é usada na cláusula WHERE de uma consulta para retornar linhas correspondentes a um regex.

A condição também é válida em uma restrição ou como uma função PL/SQL retornando um booleano. Vide exemplo:

A WHERE A BAIXO, FILTRA AS PESSOAS COM O PRIMEIRO NOME "Steven" OU "Stephen":
SELECT * FROM TABLE
WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$');
view raw regexp_like1.sql hosted with ❤ by GitHub

REGEXP_SUBSTR

Usado para obter apenas um “trexo” do texto original. Uso esta função no projeto para realizar tratamentos de exceções Oracle. Alguns exceções trás na mensagem o nome da tabela e até o nome do campos.

Para dar uma mensagem amigável para o usuário, tentamos sempre recuperar o nome da tabela e campo da mensagem ORA original, assim conseguimos mastigar a mensagem:

IMPORTANTE: O ÚLTIMO PARAMENTRO INDICA QUAL O GRUPO QUE SERÁ RETORNANDO, VIDE EXEMPLO:
RECUPERA DA MENSAGEM O NOME DA USUÁRIO/SCHEMA, RETORNANDO "SCHEMA"
SELECT REGEXP_SUBSTR('ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE_NAME"."COLUMN_NAME")','"(.*?)"',1,1) FROM DUAL;
RECUPERA DA MENSAGEM O NOME DA TABELA, RETORNANDO "TABLE_NAME"
SELECT REGEXP_SUBSTR('ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE_NAME"."COLUMN_NAME")','"(.*?)"',1,2) FROM DUAL;
RECUPERA DA MENSAGEM O NOME DA COLUNA, RETORNANDO "COLUMN_NAME"
SELECT REGEXP_SUBSTR('ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE_NAME"."COLUMN_NAME")','"(.*?)"',1,3) FROM DUAL;
view raw REGEXP_SUBSTR.SQL hosted with ❤ by GitHub

REGEXP_INSTR

Pesquisa um texto para um determinado regex e retorna um inteiro indicando a posição na string onde a correspondência foi encontrada. Você especifica qual ocorrência deseja encontrar e a posição inicial.

Esta função é semelhante ao String.indexOf() do Java, retornando a posição inicial da ocorrência.

RETORNA O NÚMERO 9, OO SEJA, POSIÇÃO INICIAL DO EMAIL NO TEXTO.
SELECT REGEXP_INSTR('E-mail: eder@gmail.com', '\w+@\w+(\.\w+)+') FROM DUAL;
view raw REGEXP_INSTR1.sql hosted with ❤ by GitHub

Por hoje é isso, espero que possa ajuda-los.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: