Expressões Regulares em Banco de Dados Oracle

Expressões regulares representam uma poderosa ferramenta para descrição e busca de padrões em diversos tipos de suporte de dados. Esses podem ser bases de dados, textos ou mesmo na própria Internet, como podemos presumir a busca, com a utilização de padrões, é uma atividade bastante comum, em diversas áreas do conhecimento, e que já possui um instrumental bastante variado em todos os tipos de sistemas, sejam eles tipicamente comerciais ou científicos. Nesse contexto situa-se a importância de ferramentas cada vez mais versáteis, quer sejam de extração de conhecimento ou de identificação de dados. Nesse ponto introduzimos uma técnica que poderá servir perfeitamente aqueles que necessitam de mais ferramentas para realizar suas pesquisas em suas bases de dados: Expressões Regulares.

Expressões Regulares já constam como recurso disponível em diversos sistemas de informação há muito tempo, para os aficionados por Unix/Linux o grep (“Global Regular Expression Print”) não é nenhuma novidade, assim como sua utilização em editores de texto e linguagens de programação( tais como Java e Perl etc...), e também já se encontra incorporado “oficialmente” aos Sistemas Gerenciadores de Bases de Dados(SGBDS) a partir dos SGBDS padrão ANSI (American National Standards Institute - Instituto Nacional Americano de Padronização) SQL99.

Para a Ciência da Computação Expressões Regulares(ERs ou do inglês abreviado regex - regular expression) provê uma forma eficiente de identificar e manusear cadeias de caracteres de interesse, como caracteres particulares, sequências alfanuméricas, padrões numéricos, etc... Dessa forma podemos, por exemplo, utilizar ERs para:

• Localizar um determinado trecho de texto que só se tem ideia das variações possíveis.
• Procurar sequências alfanuméricas que se iniciem ou terminem com caracteres conhecidos.
• Realizar busca com padrões complexos de combinações alfanuméricas.
• Validação de formatos de texto
• Filtragem de informação.

A base teórica das ERs vem de estudos da neurolinguística na década 40, assim como da matemática na década de 50, temos como um nome importante Stephen Cole Kleene, que teve grande influência na Ciência da Computação e na Matemática. Como recurso de SGBDs, as ERs, foram oficialmente adicionado pelo padrão SQL:1999(ANSI), como já mencionado, em conformidade com o padrão POSIX (Acrônimo para Portable Operating System Interface que pode ser traduzido como Interface Portável entre Sistemas Operacionais). Trata-se de uma família de normas definidas pelo IEEE (Institute of Electrical and Electronics Engineers - Instituto de Engenheiros Eletricistas e Eletrônicos)e designada formalmente por IEEE 1003.

Na data em que esse artigo estava sendo redigido vários SGBDs já ofereciam algum suporte a ERs, como o Microsoft SQL Server 2000, MySQL, PostgreSQL, Oracle10G entre outros. Infelizmente a falta de padronização na implementação de recursos por parte dos fabricantes de SGBDs é muito sentida em termos de ERs.

Esse artigo cobre a utilização de ERs no SGBD Oracle, versões 10G e 11G, com informações necessárias aos usos mais recorrentes dessas funções. Caso esteja trabalhando com outro SGBD e queira utilizasse das informações aqui encontradas, por gentileza consulte a documentação do mesmo para verificar a compatibilidade.

Caracteres especiais utilizados nas Expressões Regulares

Para utilização de Expressões Regulares, se faz necessário conhecer alguns metacaracteres (entenda por “curingas” ou “alias”) que são encontrados na utilização de ERs, nesse instante apenas citarei algumas relações mais completas poderão ser encontradas a partir das referências no final do artigo. Para utilização de expressões regulares existem vários metacaracteres que devem ser conhecidos, entre eles:

O padrão POSIX possui também classes pré-definidas que podem ser utilizados com ([ ]) – brackets. Temos como exemplos: [[:lower]], [[:digit]] entre outros, veja relação a seguir:

Como já foi colocado, os interessados em aprofundar seus conhecimentos nesse assunto podem utilizar as referências presentes no item 5 (Para saber mais ) desse artigo, lá encontraram informações de onde pesquisar as relações de completas de metacaracteres e classes disponíveis.

Busca de Expressões Regulares no SGBD Oracle

O Oracle versão 10G , ou superior, possui as seguintes funções de busca baseada no uso de expressões regulares: REGEXP_LIKE(),REGEXP_REPLACE(),REGEXP_SUBSTR() e REGEXP_COUNT(), essa última novidade da versão 11G.

REGEXP_LIKE(x, padrão[,opção_correspondente]) - Realiza a busca em x da expressão regular definida no parâmetro padrão. Opção_correspondente pode ser definida com os seguintes valores:

• ‘c’ - Especifica correspondência com diferenciação de maiúsculas e minúsculas( Padrão – case-Sensitive)
• ‘i’ - Especifica a busca sem diferenciação de caracteres maiúsculos e minúsculos(case-Insensitive)
• ‘n’ - Que permite usar o operador de correspondência com qualquer caractere.
• ‘m’ - Que trata x como uma linha múltipla.
• ‘x’ - ignora espaços em branco.

A semelhança com o comando LIKE não é mera coincidência, mas o leitor já poderá notar a maior flexibilidade no uso de REGEXP_LIKE em relação ao operador LIKE.

Considerando que a melhor maneira de aprender é com a utilização prática, estarei ilustrando a utilização com vários pequenos exemplos de uso, que poderão ser estudados e adaptados a outras necessidades.

Nesse primeiro exemplo suponhamos a necessidade de achar em uma tabela de clientes todos os clientes com data de nascimento entre 1970 e 1975. Podemos realizar a busca, usando expressões regulares, da seguinte forma:

SELECT  nome, sobrenome, data_nascimento
 FROM clientes
 WHERE REGEXP_LIKE(TO_CHAR(data_nascimento, 'YYYY'), '^197[0-5]

Devemos ter em mente que ERs, ao contrário do operador LIKE, tem a tendência de nos trazer tudo, sem a necessidade dos coringas (%), dessa forma precisamos estar atentos para restringir a informação de retorno ao desejado.

Para quem não tem ainda experiência com expressões regulares, podemos definir, como exemplo, um conjunto numérico de muitas formas, de forma que [0123456789] é igual à [0-9], esses detalhes podem tornar seu código muito mais legível e elegante, e economizar digitação é claro. No exemplo anterior poderíamos ler a expressão da seguinte forma, retorne todos os clientes (nome, sobrenome, data de nascimento), cujo ano de nascimento se inicie com 197(“^197”) e termine (“$” ) com um elemento de 0 até 5 (“[0-5]$”).

SELECT  nome, sobrenome, data_nascimento
 FROM clientes
 WHERE REGEXP_LIKE(sobrenome, '^C’, ‘i’);

Para recuperar os registros dos clientes que se chamem Flávio ou Flavio, podemos usar o seguinte o código:

SELECT  nome, sobrenome, data_nascimento
 FROM clientes
 WHERE REGEXP_LIKE(nome , '^FL[aá]’, ‘i’);

Note nesse caso que os elementos da lista “a ou “á” não possuem separadores e que a busca é case-insentive. Nesse ponto muitos podem colocar que esse mesmo tipo de busca pode ser feita por meio de LIKE em SQL ou por meio de buscas textuais. Nesse instante devemos pensar que para cada caso existe uma solução mais adequada, e que para nesses exemplos a solução mais simples elegante é realmente lançar mão das ERs, mas deixo bem claro que existirão situações que a utilização de soluções textuais, fonéticas, ou até mesma a combinação de várias dessas técnicas combinadas ainda com Ers, poderão se mostrar úteis ou até mesmo necessárias.

Continuando com nossos exemplos, podemos trazer uma seleção de linhas em que o nome possua exatamente cinco letras:

 SELECT  nome
  FROM clientes
  WHERE REGEXP_LIKE(nome , '^.....

Podemos observar que o número de caracteres desejadas também poderia ser expresso da seguinte maneira, no bloco WHERE da nossa expressão:

REGEXP_LIKE(nome, '^.{5}

Nesse momento vamos supor que queremos procurar dois nomes ao mesmo tempo, ou ‘joão’ ou ‘maria’:

SELECT  nome
 FROM clientes
 WHERE REGEXP_LIKE(nome , '(joão|maria)','i');

Estes exemplos simples tiveram como objetivo, demonstrar tanto a versatilidade quanto a simplicidade das ERS, e como sua utilização pode se expandir para expandir para muito além do nosso conhecido comando LIKE.

Não poderíamos deixar de citar que podemos lançar mão de ERs nos SGBDs para outras funções que não somente a busca, podemos utiliza-los, por exemplo para restringir entradas nos SGBDs. Para tanto podemos utilizar também as ERs para construção de constraints, por exemplo, para definir o formato que o número de telefones devem ser armazenados no SGBD, por exemplo (xxx) xxx-xxxxx, poderíamos lançar mão do seguinte comando:

ALTER TABLE cliente    
ADD (CONSTRAINT formato_numero_telefone    
CHECK (REGEXP_LIKE(numero_telefone,  '^\([[:digit:]]{3}\) 
[[:digit:]]{3}-[[:digit:]]{4}

Além de REGEXP_LIKE o Oracle disponibiliza as seguintes funções com suporte a expressões regulares:

• REGEXP_REPLACE() – O uso dessa função é para procurar um padrão e substituí-lo por um string dada
• REGEXP_SUBSTR() – Retorna parte de uma string com recursos avançados. estende as funcionalidades da função SUBSTR, deixando você pesquisar uma string para uma expressão regular padrão. Esta função é útil se você precisar o conteúdo de uma string, mas não corresponder a sua posição na string original. A função retorna a string como VARCHAR2 ou CLOB.
• REGEXP_COUNT() –Novidade do Oracle 11G - Usado para obter a quantidade de vezes que uma expressão regular ocorre em uma string.

As expressões regulares possuem várias vantagens sobre os tradicionais comandos SQLs (LIKE, INSTR, SUBSTR e REPLACE) desses apenas o operador LIKE possui facilidades de busca ( % e _ ), e mesmo assim não suporta mecanismos de busca de padrões ou expressões complexas, sendo muito difícil reproduzir expressões regulares complexas com comandos SQL.

E quanto a índices?

Todos sabemos que os índices são um fator muito importante na busca de informações em função do melhor desempenho que podem proporcionar as buscas. Assim como com a utilização em SGBDs do comando LIKE , os índices padrões criados em um determinado campo não são utilizados nas buscas por ERs, no entanto, isso é muito importante, você pode criar índices baseados em função para implementar desempenho em suas consultas com ERs, caso contrário suas consultas com ERs farão uma varredura completa na tabela(full table scan) o que poderá causar contenção no banco de dados.