MySQL - REGEXP (EXPRESSÕES REGULARES) no MySQL

Expressões regulares são um tema, diriamos, meio "cabeludo" pois a sua sintaxe não ajuda muito no quesito "dicas de como funciona". Geralmente trabalhar bem com REGEXP significa muito tempo de prática para "pegar o jeito" mais do que decorar livros ou soluções prontas. Mas não é por isto que vamos desanimar de trabalhar com elas, pois são extremamente poderosas.

O que alguns podem não saber, ou sabem mas não estão familiarizados, é que podemos utilizar também nos bancos de dados as famosas REGEXPs, alguns com mais outros com menos restrições. As REGEXPs também nos passam a falsa ilusão de que podemos utilizá-las não só para filtrar dados mas também para pegar "pedaços" da informação já selecionada. Vamos tratar disto mais adiante.

Nosso foco hoje é ver um pouco das expressões regulares aplicadas no MySQL, as armadilhas, equivocos e soluções desta ferramenta tão poderosa.

Infelizmente o MySQL não aceita todas as REGEXPs que, por exemplo, o PHP aceita. Abaixo apresento algumas das que podem ser utilizadas:

  • ^ Início de string
  • $ Fim de string
  • . Qualquer character único
  • [...] Qualquer caractere listado entre colchetes
  • [^...] Qualquer caractere não listado entre colchetes
  • p1|p2|p3 Revezamento; combina qualquer dos padrões p1, p2, or p3
  • * Zero ou mais instâncias do elemento precedente
  • + One ou mais instâncias do elemento precedente
  • {n} n instâncias do elemento precedente
  • {m,n} m até n instâncias do elemento precedente

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Vamos começar assumindo nossa tabela padrão (tabela 'site').


id nomeurltipo
1Wikipedia Homehttp://www.wikipedia.org/home
2Wikipedia NASAhttp://en.wikipedia.org/wiki/NASAarticle
3Wikipedia Planetashttp://pt.wikipedia.org/wiki/Planetaarticle

Existem três abordagens que podemos seguir que descrevo abaixo:

USANDO WHERE e REGEXP

Uma é a seleção de resultados baseado em um REGEXP feito da cláusula WHERE.

Alguns regexps podem não funcionar da mesma maneira que no javascript ou PHP. Vale fazer testes e também, sempre que possível, utilizar várias referências (não tão genéricas) para que o MySQL consiga encontrar o que você quer.


SELECT * FROM `site` WHERE url REGEXP 'http\:\/\/[a-z]{2}\.wikipedia'
idnomeurltipo
2Wikipedia NASAhttp://en.wikipedia.org/wiki/NASAarticle
3Wikipedia Planetashttp://pt.wikipedia.org/wiki/Planetarticle

A query abaixo traz o mesmo resultado só que de maneira mais genérica.


SELECT * FROM `site` WHERE url REGEXP 'http\:\/\/[a-z]{2}\\.'

O que estamos dizendo para o MySQL é que ele traga tudo que possui 'http://', depois disto tudo que tenha duas letras minúsculas e, finalmente, um ponto depois destas duas letras. A diferença entre as duas queries está no fato de, na primeira, especificarmos que queremos após o ponto '.' a palavras 'wikipedia', o que no caso dos dados da tabela não faz diferença.

Bem, esta é a idéia básica ao utilizar REGEXP no WHERE. Faça testes e variações disto.

USANDO REGEXP no SELECT

Podemos utilizar o REGEXP dentro do resultado de um SELECT também, porém, neste caso, não será possível retornar pedaços do resutado selecionados com base na extressão regular. Aliás este é um erro bem comum de muitas pessoas, imaginar que o REGEXP consegue fazer a mesma função no MySQL que um preg_match() no PHP. Infelizmente isto não é possível. O que conseguimos como retorno é tão somente um boolean 1 ou 0 indicando se o padrão foi encontrado ou não.


SELECT *,url REGEXP 'http\:\/\/[a-z]{2}\\.' AS existe FROM site
idnomeurltipoexiste
1Wikipedia Homehttp://www.wikipedia.org/home0
2Wikipedia NASAhttp://en.wikipedia.org/wiki/NASAarticle1
3Wikipedia Planetashttp://pt.wikipedia.org/wiki/Planetaarticle1

O REGEXP acima é o mesmo que do exemplo anterior, só que ao invés de ser declarado no WHERE ele é declarado no SELECT.

Para conseguir este tipo de retornom, ou seja, pegar um pedaço da string retornada pelo SELECT, devemos utilizar as funções de string como SUBSTRING_INDEX(), RIGHT() e etc. Este assunto merece um artigo à parte mas você pode consultar o link abaixo para ter uma idéia do que fazer.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

NOT REGEXP

NOT REGEXP atua como um inversor, ou negação, do REGEXP, ou seja, se REGEXP retorna 'A' NOT REGEXP, com a mesma sintaxe, retorna qualquer outra coisa menos 'A'.


SELECT * FROM `site` WHERE url NOT REGEXP 'http\:\/\/[a-z]{2}\.wikipedia'
idnomeurltipo
1Wikipedia Homehttp://www.wikipedia.org/home

O comando acima retorna a seleção inversa de "REGEXP 'http\:\/\/[a-z]{2}\.wikipedia'" do primeiro exemplo graças ao NOT REGEXP.

Bem, é isso ai.
Abraços

Comentários

Postar um comentário

Postagens mais visitadas deste blog

MySQL - Completando quantidades fixas de caracteres com as funções LPAD() e RPAD()

Em PHP temos uma função não tão conhecida, str_pad() , que completa uma string com qualquer caractere até uma quantidade definida pelo programador. Esta função é extremamente útil no caso de codificações padronizadas onde a quantidade de caracteres deve ser sempre fixa. No MySQL temos uma função equivalente chamada LPAD() . A sintaxe desta função é LPAD(CAMPO,QUANTIDADE DE CARACTERES,CARACTERE PARA COMPLETAR) Vamos fazer uma query na tabela exemplo 'produtos' abaixo: cod | nome | valor 3 | Iphone | 3000.00 10 | Blue Ray | 1800.00 230| Book | 30.00 Vamos supor que o padrão de código exigido pelo sistema que lê o banco de dados seja de cinco (5) caracteres. Valores menores que este deverão ser preenchidos com zeros a esquerda SELECT LPAD(cod,5,'0') AS cod, nome,valor FROM produtos Teremos como resultado: cod | nome | valor 000003 | Iphone | 3000.00 000010 | Blue Ray | 1800.00 000230 | Book | 30.00 Assi
Leia mais

MySQL - Clonando tabelas na linha de comando

Clonar tabelas é fácil se você tiver a disposição um ' PHP MyAdmin ' ou uma aplicação gráfica instalada em sua máquina. Porém existem casos em que você só possui acesso a um shell (ou terminal) ou mesmo precisa criar uma aplicação que em algum momento seja necessária a clonagem de uma tabela. Os procedimento é muito simples: 1º Criar um clone da estrutura original (LIKE banco_de_dados.tabela) CREATE TABLE recipes_new LIKE production.recipes; 2º Inserir o conteúdo original dentro do clonado (FROM banco_de_dados.tabela) INSERT recipes_new SELECT * FROM production.recipes; É simples assim! Abraços
Leia mais

PHP - Gerando arquivo em UTF-8 com fwrite() e utf8_encode()

Encontrei uma dica muito interessante do "cutmaster at fearlesss dot com" que postou no site php.net. Ele mostrou como gerar um arquivo realmente no formato UTF-8. Segue a tradução de seu post. "Para aqueles que, como eu, gastaram muito tempo para entender porque fwrite() não criava arquivos utf-8 de verdade, aqui vai uma explicação que encontrei: Tentei fazer algo com:" <?php myString = utf8_encode("Test with accents éèàç"); fh=fopen('test.xml',"w"); write($fh,$myString); close($fh); "Por alguma razão misteriosa, o arquivo gerado traz os acentos sem o padrão utf-8. Tentei binário, etc. etc. e finalmente encontrei a solução. Parece que fwrite() PRECISA ter a função utf8_encode() DENTRO de seus parâmetros, como segue abaixo." <?php $myString = "Test with accents éèàç"; $fh=fopen('test.xml',"w"); fwrite($fh,utf8_encode($myString)); fclose($fh);
Leia mais