Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como utilizar expressões regulares (RegExp) no seu banco de dados

Visualizações: 30.028 views
Tempo de Leitura: 15 minutos

Olá pessoal!
Animados para mais um post ???

Nesse artigo eu gostaria de demonstrar para vocês como podemos utilizar expressões regulares (RegExp) no seu banco de dados através de consultas, sejam elas para criar algum monitoramento ou mesmo criar relatórios e indicadores de BI ou algum sistema.

A expressão regular (ou os estrangeirismos regex ou regexp, abreviação do inglês regular expression) provê uma forma concisa e flexível de identificar cadeias de caracteres de interesse, como caracteres particulares, palavras ou padrões de caracteres. Em outras palavras, a expressão regular é uma forma de permitir realizar, de forma simples, operações com strings extremamente complexas, que demandariam várias condições para tratar esses dados.

Como vocês devem saber, o dialeto do Regexp não é algo que é senso comum entre as linguagens de programação. Existem várias implementações diferentes e as regras da expressão regular são similares, mas podem mudar entre uma linguagem e outra. Ex: Uma expressão regexp que funciona no .NET pode não funcionar no PHP ou no Java e vice-versa.

No SQL Server, podemos utilizar 2 dialetos para utilizar nossas expressões regulares:

  • .NET: Dialeto disponível utilizando o SQLCLR (disponível a partir do SQL Server 2005) e a biblioteca system.text.regularexpression do .NET Framework
  • ECMA: Dialeto disponível utilizando OLE Automation (disponível a partir do SQL Server 2000) e o módulo VBScript.RegExp. Esse dialeto é o mesmo utilizado pelo Javascript

Para termos de comparação:

  • Regexp nativo do SQL Server (utilizando LIKE ou PATINDEX): Limitado (quando comparado aos dialetos mais comuns) e tem a melhor performance entre os 3
  • .NET: Tão completo quanto o dialeto ECMA (ou seja, possui MUITO mais recursos que o nativo do SQL Server), tem a performance um pouco abaixo do nativo do SQL Server, mas MUITO (muito mesmo) acima do ECMA (por conta do OLE Automation, não do dialeto em si).
  • ECMA: Tão completo quanto o dialeto .NET (ou seja, possui MUITO mais recursos que o nativo do SQL Server) mas tem uma performance muito ruim quando comparado aos outros 2. A sua vantagem sobre o .NET é que não exige a criação de objetos SQLCLR e está disponível no SQL Server 2000.

O intuito desse post é demonstrar como utilizar expressões regulares no SQL Server e mostrar alguns exemplos simples sobre isso. O foco aqui, não é te tornar especialista em expressão regular. Regexp não foi criado para ser facilmente entendido. Requer muito esforço e estudo para dominar essa linguagem. Para isso, sugiro o excelente site Regular-Expressions.info e também o site brasileiro do Aurélio Jargas.

Para ajudar a entender o uso das expressões regulares (dialetos .NET e ECMA), segue imagem abaixo, que extraí do site RegExLib:

Alternativa #1: LIKE e PATINDEX

Utilizando essa solução nativa do SQL Server, você poderá utilizar expressões regulares simples, mas bem úteis no seu dia a dia, e com uma boa performance de execução. A vantagem desse método, é que não requer nenhuma permissão adicional no banco, nem a necessidade de habilitar nenhum recurso avançado ou criar novos objetos no banco.

Visualizar conteúdo
Muitas pessoas ainda não sabem, mas o SQL Server possui suporte nativo a utilizar algumas expressões regulares (RegExp) através dos operadores LIKE e PATINDEX, conforme vou demonstrar alguns exemplos abaixo:

Case sensitive

Filtrando a primeira letra

Filtrando as primeiras letras + Case sensitive

Aplicando um filtro personalizado e específico

Utilizando o operador de negação (^)

Utilizando o operador de escape

Identificando caracteres especiais

Precisa remover caracteres especiais? Saiba mais acessando o post Como remover acentuação e caracteres especiais de uma string no SQL Server.

Utilizando números

Validando e-mails

Resultado:

Recuperando apenas a parte numérica de uma string

Exemplo:

Quer saber mais sobre validação de dados? Acesse os posts abaixo:
Validando CPF, CNPJ, E-mail, Telefone e CEP no SQL Server
Como validar a inscrição estadual para todos os estados utilizando o C# (CSharp) e o SQL Server CLR
Como validar inscrição estadual usando função T-SQL no SQL Server

Alternativa #2: Dialeto .NET com SQLCLR

Com esse método, você terá acesso a utilizar expressões regulares do .NET Framework, que apresenta alta performance e todos os recursos disponíveis do dialeto, utilizando SQLCLR e C#. Está disponível desde o SQL Server 2005.

Caso você queira saber mais sobre o SQLCLR, não deixe de ler os artigos abaixo:
Introdução ao SQL CLR (Common Language Runtime) no SQL Server
SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function
Outros posts sobre SQLCLR

Visualizar conteúdo

Exemplos da fncRegex_Match

Identificando palavras repetidas

Validando uma máscara numérica específica (CEP)

Validando CPF e CNPJ (apenas formato, sem DV)

Exemplos da fncRegex_Find

Recuperar trechos onde a palavra “Dirceu” aparece no texto

Encontrando palavras repetidas seguidamente

Retornando um resultset com quebras de linhas (split)

Recuperar apenas datas válidas

Identificando placas de veículo em uma string

Exemplos da fncRegex_Replace

Removendo caracteres especiais

Removendo tags HTML

Converter dados tabulares para comando de INSERT no banco

Remover palavras duplicadas

Código-fonte C# das funções

Caso você queira utilizar essas funções no seu próprio projeto SQLCLR, pode utilizar os códigos-fonte disponibilizados abaixo:

fncRegex_Replace

fncRegexp_Match

fncRegexp_Find

Código-fonte T-SQL das funções

Caso você não tenha muita familiaridade com C# ou projetos no Visual Studio, mas mesmo assim quer utilizar expressões regulares no SQL Server, vou disponibilizar o código T-SQL abaixo, que vai te permitir criar o assembly e as funções no seu banco de dados, sem ter muito esforço, apenas apertando “F5” nesse script:

Alternativa #3: Dialeto ECMA com OLE Automation e VBScript

Utilizando o método do OLE Automation, você terá acesso à todos os recursos do dialeto ECMA no seu SQL Server, e poderá criar expressões regulares bem complexas. O custo disso, é que o uso do OLE Automation pode deixar a execução do seu código mais lento que as outras opções, mas a sua implementação é mais simples que a alternativa utilizando SQLCLR.

Disponível desde a versão 2000, o OLE Automation pode causar algumas instabilidades na sua instância devido à conhecidos problemas de gerenciamento de memória pelo SQL Server, um dos motivos que levou a indicação de substituição desse recurso pelo SQLCLR. O recado é: Use com moderação.

Quer saber mais sobre o OLE Automation? Não deixe de ler esses artigos abaixo:
Habilitando OLE Automation via T-SQL no SQL Server
Operações com arquivos utilizando OLE Automation no SQL Server
Outros posts sobre OLE Automation

Visualizar conteúdo
Uma alternativa ao operador LIKE do SQL Server, que, apesar de oferecer uma boa performance (relativamente) e alguns recursos legais, ainda é um pouco limitada e não oferece suporte a boa parte dos operadores de expressão regular mais complexos, podemos utilizar o dialeto ECMA (o mesmo utilizado pelo Javascript) com a ajuda do recurso OLE Automation.

Como ativar o OLE Automation

Para ativar o OLE Automation na sua instância, basta executar o comando abaixo:

Para conhecer meus outros artigos sobre OLE Automation, acesse este link aqui.

Para esse tópico, vou utilizar as excelentes funções do Phil Factor RegexReplace, RegexMatch e RegexFind (código fonte após os exemplos).

Exemplos de uso da RegexMatch

Identificar palavras repetidas

Identificar palavras próximas

Validando uma máscara numérica específica (CEP)

Validando CPF e CNPJ (apenas formato, sem DV)

Exemplos de uso da RegexFind

Recuperar trechos onde a palavra “Dirceu” aparece no texto

Encontrando palavras repetidas seguidamente

Retornando um resultset com quebras de linhas (split)

Quebrar linhas para cada palavra da frase (split)

Recuperar apenas datas válidas

Identificando placas de veículo em uma string

Exemplos de uso da RegexReplace

Identifica URL e encapsula como link html – tag A

Remover strings HTML de um texto

Converter dados tabulares para comando de INSERT no banco

Remover palavras duplicadas

Removendo caracteres especiais

Código da função RegexFind

Código da função RegexMatch

Código da função RegexReplace

Nesse post, pudemos ver o quanto as expressões regulares (Regexp ou Regex) podem ser úteis no nosso dia a dia, seja você um DBA, analista de BI ou desenvolvedor. Também demonstrei como é fácil utilizar esse poderoso recurso no SQL Server, seja utilizando o LIKE ou PATINDEX (funções nativas do SGBD) ou utilizando outros recursos, como OLE Automation e SQLCLR.

Espero que esse post tenha esclarecido algumas dúvidas sobre Regexp e tenha mostrado um novo e incrível recurso para quem ainda não o conhecia.

Um abraço e até o próximo post!