NUNCA MAIS PASSE RAIVA POR NÃO CONSEGUIR RESOLVER UM PROBLEMA COM O EXCEL - GARANTIDO!

UNIVERSIDADE DO VBA - Domine o VBA no Excel Criando Sistemas Completos - Passo a Passo - CLIQUE AQUI

Você está em: PrincipalArtigosWindows : Regexpsql2k
Quer receber novidades e e-books gratuitos?
EXPRESSÕES REGULARES NO MICROSOFT SQL SERVER 2000

Sobre o Autor:
Lazaro Fernandes Lima é WebDeveloper em ASP/ASP.NET/SQL, trabalha com desenvolvimento de portais B2B desde 2006 no www.sindiconet.com.br, um dos maiores portais B2B do Brasil.

EXPRESSÕES REGULARES NO MICROSOFT SQL SERVER 2000
Lazaro Fernandes Lima

TÓPICOS

  • UMA BREVE HISTÓRIA
  • EXPRESSÕES REGULARES NO SQL SERVER 2000
  • ENTENDENDO E REGISTRANDO A DLL
  • MÃO NA MASSA
  • CONCLUSÃO

Olá a todos, meu nome é Lazaro Fernandes Lima.

Neste artigo irei apresentar o uso de expressões regulares em SQL SERVER 2000. O artigo se destina a profissionais que utilizam SQL SERVER 2000 ou estudantes da área, também é recomendado, porém, não necessário, o conhecimento sobre expressões regulares.

Como este é meu primeiro artigo sobre SQL SERVER 2000 e o assunto é muito pouco explorado, vou começar com uma breve história sobre Expressões Regulares (Regular Expressions ou simplesmente RegEx), suas utilidades mais comuns e o porquê de Expressões Regulares no SQL SERVER 2000.

Não irei me estender muito sobre a história do assunto, o que pretendo é transmitir interesse ao amigo leitor e é claro, demonstrar que expressões regulares podem facilitar diversos processos de manutenção além de muitas outras aplicações.

UMA BREVE HISTÓRIA

No “início”, por volta de 1943, dois neurologistas publicaram um estudo do qual teorizavam o funcionamento de nossos neurônios.

Alguns anos se passaram e Stephen Cole Kleene, matemático, descreve algebricamente os modelos desse estudo utilizando símbolos para representar seus grupos regulares (regular sets). E assim surgem as expressões regulares.

Só em 1968, Ken Thompson traz as expressões regulares para os computadores, através do editor QED. Porém em 1986 um pacote pioneiro em linguagem C chamado regex tratava das expressões regulares, qualquer um poderia utilizá-lo em seus programas, de graça. Desde então o uso das expressões regulares se tornou muito comum.

E assim finalizo nossa breve história.

EXPRESSÕES REGULARES NO SQL SERVER 2000

Depois da emocionante história das expressões regulares, vamos tratar delas no banco de dados MICROSOFT SQL SERVER 2000.

Primeiro, abra o Books Online, digite “regular expressions” (sem as aspas duplas). Segundo, feche-o, afinal de contas nada foi encontrado. Exatamente, nada foi encontrado, isto porque o SQL SERVER 2000 não tem suporte as Expressões Regulares no T-SQL.

Então o amigo leitor me pergunta:

- Lazaro, você me fez ler até aqui para me dizer que não posso utilizar expressões regulares no SQL SERVER 2000?

Calma! Não disse que não se poderia utilizar, disse que no T-SQL do SQL SERVER 2000 não existe essa possibilidade. Até existe, mas não considero como correto dizer que existe o uso de expressões regulares no T-SQL, mas que é limitado aos LIKEs que conhecemos.

Segue abaixo duas tabelas retiradas do livro Beginning Regular Expressions 2005, pela Wrox Editora (Recomendo).

Metacaracteres suportados pelo SQL SERVER 2000 utilizando o LIKE.

suported.jpg

Metacaracteres ou funcionalidades não suportadas no LIKE.

notsuported.jpg

Perceba que existem grandes lacunas pelo T-SQL do SQL SERVER 2000 quanto a este assunto!

Para isso podemos utilizar uma DLL criada em uma linguagem como a C++, do qual nos fornece toda a funcionalidade necessária para que possamos trabalhar com expressões regulares.

Para que possamos utilizar essa DLL precisamos registrá-la como uma Extended Stored Procedure (Extended Stored Procedure permite a integração de programas desenvolvidos em C/C++ e o SQL SERVER. As xps, depois de instaladas podem ser chamadas normalmente utilizando a mesma sintaxe das Stored Procedures em T-SQL, para maiores informações consulte o Books Online).

ENTENDENDO E REGISTRANDO A DLL

A DLL foi desenvolvida em C++, todo o projeto está documentado e pode ser baixado através do codeproject livremente. Segue link do projeto, http://www.codeproject.com/managedcpp/xpregex.asp

A DLL está disponível na pasta Release do projeto.

Agora que temos o projeto e a DLL em mãos possuímos todo o conhecimento necessário para utilizá-la. Vamos agora registrar nossa DLL como uma Extended Stored Procedure do SQL SERVER 2000.

1 - Coloque a DLL xpRegex.dll no diretório (Microsoft SQL Server\MSSQL\Binn).
2 - Execute o comando abaixo no Query Analyser para registrar a DLL no servidor.

USE master
GO
EXEC sp_addextendedproc 'xp_regex_format', 'xpRegex.dll'

Explicando o código.
sp_addextendedproc – Insere uma Extend Stored Procedure ao servidor. Dois parâmetros são utilizados, [Nome] e [DLL].
Somente usada no MASTER, pois as Extended Stored Procedures somente podem ser criadas e utilizadas apartir deste banco de dados.

3 - Execute o comando abaixo no Query Analyser com o seu banco de dados para que possamos adicionar a função que irá utilizar a nossa Extended Stored Procedure.

CREATE FUNCTION [dbo].[fn_regex_format]
(
      @input VARCHAR(8000),        -- the input text
      @pattern VARCHAR(8000),      -- a regular expression
      @result VARCHAR(8000)        -- how to format the output
)
RETURNS VARCHAR(8000) AS 
BEGIN
      DECLARE @captureText VARCHAR(8000)
      EXEC master.dbo.xp_regex_format @input, @pattern, @result, @captureText OUT
      RETURN @captureText
END
GO

Acredito que maiores explicações não são necessárias sobre a criação desta função.

Agora que registramos nossa DLL execute o seguinte teste para verificar se esta tudo funcionando perfeitamente.

CREATE TABLE #CEP(CEP   VARCHAR(9))
INSERT #CEP VALUES ('12345-678')
INSERT #CEP VALUES ('1234S678')
INSERT #CEP VALUES ('12343-678')
INSERT #CEP VALUES ('12345678')
INSERT #CEP VALUES ('12345X678')
SELECT dbo.fn_regex_format(CEP,'(\d{5}-\d{3})|(\d{8})','1')
FROM #CEP

O retorno deve ser igual à imagem abaixo.

first_test.jpg

Sendo que, todo valor de retorno igual nulo (NULL) é inválido, ou seja, a expressão regular acusou formato inválido de CEP para dois registros de forma correta e consistente.

Explicando a expressão regular.

\d{5}     = defino que a busca deve retornar toda string que contenha cinco(5) dígitos numéricos.
-         = simplesmente defino que terá um traço(-) naquela posição.
|         = este é o operador OR(OU) da expressão regular.
()        = define que aquele conteúdo será usado posteriormente através de marcadores pré-definidos. No caso, acabei não usando esse valor para não complicar (iria retornar a string dentro dos parênteses, explicarei mais adiante), utilizei somente o valor “1” fixo para o retorno, do qual, somente retorna algo se o valor passar na verificação da nossa expressão regular (no caso, o parâmetro que defini foi retornado somente quando o CEP passou na verificação, por isso o retorno no grid foi “1” para os válidos).

Não irei me estender muito à explicação das sintaxes adiante, irei somente enfatizar pontos importantes e que são extremamente necessários ao entendimento do que iremos fazer.

MÃO NA MASSA

Para que possamos nos identificar ainda mais com as expressões regulares, iremos utilizá-las para validar uma base de CEPs, algo que pode dar certa dor de cabeça para novos desenvolvedores, mas que será transmitida aqui de foram simples.

Iremos considerar que durante anos foram inseridos CEPs em nossos formulários de forma ineficiente, o que permitiu CEPs com as seguintes características:

- Em branco
- Números repetidos
- Ceps incompletos
- Ceps em formatos distintos (00.334=444)

O correto seria literalmente desconsiderar estes Ceps, porém no nosso problema precisamos ter o máximo de Ceps possíveis para uso posterior, e devemos considerar a “criatividade” nos cadastros de ceps no nosso site.

Resumindo... Temos 20mil Ceps cadastrados e desejamos ter uma relação de válidos x inválidos.

Iremos criar uma tabela temporária que irá armazenar o código do nosso registro que contém o CEP (código do usuário, por exemplo), CEP original, CEP já validado e situação. O campo situação irá somente classificar os válidos e inválidos.

1 – Insira os dados na tabela temporária #CEPS_VALIDOS utilizando a expressão regular para validar o CEP.
2 – Classifique os inválidos como os registros com valor NULL no CEP e valores repetidos (utilizando WHERE convencional).

Pronto.

Até parece mágica, mas isto é possível com o uso de expressões regulares no SQL SERVER 2000.

Abaixo o script de inserção com a expressão regular, veja que existem algumas particularidades nesta expressão regular.

INSERT #CEPS
SELECT    usuario_codigo,
          usuario_cep,
dbo.fn_regex_format(usuario_cep,'(\d{2})\.?(\d{3})[?;:|.+_= -]?(\d{3})|(\d{8})','$1$2$3$4'),
     null
FROM usuarios
order by 3

Nossa expressão regular está extremamente simples (\d{2}).?(\d{3})[?;:|.+_= -]?(\d{3})|(\d{8}), considerei que pode ou não ter um ponto(.), pode ou não ter cada um destes caracteres (;:|.+_= -), todos na suas devidas posições.

A única diferença são os parênteses( () ) que são parâmetros de retorno e os valores de saída ($1$2$3$4) que representam a ordem de apresentação dos parâmetros de retorno, sendo que, a ordem se inicia em $1, ela é definida automaticamente por cada parâmetro de retorno.

Obs: O quarto parâmetro e a verificação “|(\d{8}})” na verdade não são necessários, estão somente para ter semelhança ao primeiro exemplo.

Para que possamos entender como estes parâmetros funcionam acompanhe o exemplo abaixo:

CREATE TABLE #CEPS (CEP   VARCHAR(12))
INSERT #CEPS VALUES ('12.333=444')
INSERT #CEPS VALUES ('12333=444')
INSERT #CEPS VALUES ('12P333=444')
INSERT #CEPS VALUES ('1233355=44')
INSERT #CEPS VALUES ('12533424')
INSERT #CEPS VALUES ('12.333444')
INSERT #CEPS VALUES ('12.3433444')
INSERT #CEPS VALUES ('12333444')
INSERT #CEPS VALUES ('12S333444')
INSERT #CEPS VALUES ('12333S444')
INSERT #CEPS VALUES ('12333-444')
CREATE TABLE #CEPS_VALIDOS (CEP VARCHAR(12))
INSERT #CEPS_VALIDOS
SELECT dbo.fn_regex_format(CEP,'(\d{2})\.?(\d{3})[?;:|.+_= -]?(\d{3})|(\d{8})','$1$2$3$4')
FROM #CEPS

SELECT * FROM #CEPS_VALIDOS

-- ou
DELETE #CEPS_VALIDOS
INSERT #CEPS_VALIDOS
SELECT dbo.fn_regex_format(CEP,'(\d{2})\.?(\d{3})[?;:|.+_= -]?(\d{3})|(\d{8})','($1)$2=$3*$4')
FROM #CEPS
SELECT * FROM #CEPS_VALIDOS

Percebam que, com o uso dos parâmetros posso “moldar” a saída que desejar. Isto nos permite grande flexibilidade e facilidade no controle sobre os dados válidos.

CEPS_Tratados.gif

Ou

CEPS_Tratados2.gif

Bom, apartir de agora podemos fazer outras verificações sem a necessidade de expressão regular, como um simples WHERE para comparar se o CEP é 11111111 ou 22222222 e assim por diante.

CONCLUSÃO

Com o uso de expressões regulares podemos validar dados se forma mais simples, eficiente e segura.

Espero que tenham gostado, este assunto ainda é muito pouco explorado e raramente existe um artigo em português sobre o assunto.

Um ponto importante a ressaltar é que o uso de uma Extended Stored Procedure não é tão rápido quanto se pode imaginar, a própria expressão regular, quando mal desenvolvida, pode também garantir segundos ou até minutos a mais de execução.

As expressões regulares que utilizei são somente exemplos, sendo que existem mais regras a considerar nestas mesmas expressões regulares que poderia estar utilizando, preferi não estender a expressão regular para não confundir aqueles que estão iniciando seu aprendizado em expressões regulares e além é claro, de não ser necessário ao entendimento do artigo.

Como já mencionei anteriormente, um bom livro para aprender expressões regulares é Beginning Regular Expressions 2005, pela Wrox Editora, sites como www.aurelio.net e http://www.codeproject.com/dotnet/regextutorial.asp.

Um bom programa para criar suas próprias expressões regulares é o Expresso 3.0.

Qualquer dúvida sobre o assunto envie um email para webmasterlazaro@gmail.com, sugestões para novos artigos sobre este assunto são muito bem vindas.

Até mais.


Confira todos os artigos de Windows e Redes:

1 Como Criar Servidores Web com o Windows Server
1 Instalando o Google Chrome Com SCCM 2012 R2
1 Como Mapear Unidade De Rede Com Drive Maps
1 Como Utilizar Grupos Restritos No Active Directory
1 Como configurar o Client Push Installation no System Center
1 Como Criar uma Collection no System Center
1 Como Criar Atalho Na Área De Trabalho Com GPO
1 Como configurar a GPO para o System Center 2012 R2
1 Ninite Software Deployment
1 Guia de Certificação Microsoft
1 Como Alterar A Mascara de Rede No Escopo do DHCP
1 Como Criar uma GPO com WMI Filter
1 Como fazer uma migração de DHCP
1 Como Criar Pacotes de Instalação Automáticos e Silenciosos
1 Certificação ITIL
1 Como fazer download e instalar o VMWare Workstation 11
WINDOWS 7
1 Como Instalar o Windows 7 Automaticamente
1 Como Formatar o Windows 7
1 Os melhores Antivírus para Windows 7
1 Os melhores Aplicativos para Windows 7
1 Os melhores recursos do Windows 7
1 Como usar o Windows 7 para facilitar o seu dia a dia
1 Como escolher entre o Windows 7 e 8
WINDOWS 8
1 Como Formatar o Windows 8
1 Os melhores Antivírus para Windows 8
1 Os melhores Aplicativos para Windows 8
1 Os melhores recursos do Windows 8
1 Como usar o Windows 8 para facilitar o seu dia a dia
1 Principais diferenças entre o Windows 8 e 7
WINDOWS 2003 SERVER
1 Windows 2003 - DNS - Instalação do DNS e Criação de Zonas
1 Windows 2003 - DNS - Configurando as Propriedades de uma Zona
1 Windows 2003 - DNS - Configurando as Propriedades do Servidor DNS
1 Windows 2003 - DNS - Integração do DNS com o Active Directory
1 Windows 2003 - DNS - Atualização Dinâmica
1 Windows 2003 - DNS - Zonas de Pesquisa Inversa
1 Windows 2003 - MBSA - Microsoft Baseline Security Analyzer
1 Windows 2003 - Permissões de Compartilhamento
1 Windows 2003 - Permissões e Compartilhamento de Impressoras
1 Windows 2003 - Permissões NTFS
1 Windows 2003 - WINS - Introdução ao WINS
1 Windows 2003 - WINS - Instalação do WINS
1 Windows 2003 - WINS - Configurando as Propriedades do Servidor WINS
1 Windows 2003 - WINS - Replicação do WINS
1 Windows 2003 - DHCP – Instalação
1 Windows 2003 - DHCP - Configuração de Escopos
1 Windows 2003 - DHCP - Backup e Restore
1 Windows 2003 - DHCP - Compactando a Base de Dados do DHCP
1 Windows 2003 - DHCP - Auditoria no DHCP
1 Windows 2003 - Autenticação
1 Windows 2003 - Modelos de Segurança
WINDOWS - GERAL
1 Saiba Porque o Windows XP se Tornou uma Ameaça
1 Acesso Remoto [vídeo]
1 Como se preparar para os Exames da Microsoft [vídeo]
1 Cenários para utilização do Windows AIK - Parte 2
1 Cenários para utilização do Windows AIK - Parte 1
1 Windows AIK - Kit de Instalação Automática
1 Introdução ao MDT - Microsoft Deployment Toolkit
1 Implementando Infraestrutura entre Matriz e Filial - Parte 1
1 Implementando Infraestrutura entre Matriz e Filial - Parte 2
1 Implementando Infraestrutura entre Matriz e Filial - Parte 3
1 Implementando Infraestrutura entre Matriz e Filial - Parte 4
1 Caminhos de upgrade para o Windows Vista
1 Instalação do Windows Vista
1 Windows Vista Upgrade Advisor
1 Configurações básicas do Windows Mail
1 Contas de usuário no Windows Vista
1 Contatos do Windows
1 Cotas em disco no Windows Vista
1 Discos básicos no Windows Vista
1 Discos dinâmicos no Windows Vista
Seta Vídeo: Usando Sysprep e Ghost
Seta Vídeo: Instalando o Windows XP de forma automatizada
Seta Vídeo: Baixando atualizações do Windows e Integrando na Instalação – Parte 2
Seta Vídeo: Baixando atualizações do Windows e Integrando na Instalação – Parte 1
Seta Vídeo: Criando usuários e grupos no Active Directory com scripts automatizados
Seta Vídeo: RunAs Powerful - Script super poderoso
Seta Certificação - MCTS Windows 7
Seta Vídeo: Instalação do Serviço DNS - Parte 1
Seta Vídeo: Office Communications Server 2007 R2 – Parte 1
1 Virtual PC 2007 - Parte 2 - Instalação do Virtual PC 2007
1 Virtual PC 2007 - Parte 1 - Introdução ao Virtual PC 2007
ACTIVE DIRECTORY
1 Tutorial de Active Directory - Parte 5
1 Tutorial de Active Directory - Parte 4
1 Tutorial de Active Directory - Parte 3
1 Tutorial de Active Directory - Parte 2
1 Tutorial de Active Directory - Parte 1
1 Aprenda a Usar o VMware - Parte 2
1 Aprenda a Usar o VMware - Parte 1
1 Saiba Como Ter Boletos Bancários em Seu Site
1 Alterando o Texto do Botão Iniciar
1 System Restore (Restauração do Sistema)
1 Recovery Console (Console de Recuperação)
1 Pecados Mortais no uso do E-mail - Parte 2
1 Pecados Mortais no uso do E-mail
1 SUS - Implementação e Administração - Parte 2
1 SUS - Implementação e Administração - Parte 1
1 Windows Server 2003 - O novo recurso Shadow Copies
1 Tutorial sobre Profiles
1 Criptografia no Windows 2000 Server e o Agente de Recuperação
1 Tudo sobre Permissões de Segurança: Compartilhamento e NTFS
1 Implementando o DFS: Distributed File System
1 Boot no Windows 2000/XP e o arquivo Boot.ini
1 Gerenciamento de Discos e Armazenamento
WINDOWS 2000
1 DHCP no Windows 2000 Server
1 DNS no Windows 2000 Server
1 Implementação e Administração do SUS
1 Serviço RRAS no Windows 2000 Server
1 Introdução às redes com Windows 2000 – Parte 1
1 Introdução às redes com Windows 2000 – Parte 2
1 Introdução às redes com Windows 2000 – Parte 3
1 Primeiros passos com Ajax Control Toolkit
1 Faça a sua declaração de imposto de renda com Linux
1 Sistema de Controle de Gasolina Mobile
1 Série Visual Source Safe - Parte 1
1 Extraindo dados para arquivo texto
1 Como exibir informações com múltiplas instruções SQL
1 Expressões Regulares No Microsoft SQL Server 2000
1 Cursores
1 O Namespace My
1 JOB no SQL Server
1 Criando uma aplicação Windows Service
1 Criando Class Libraries com VB.NET - Parte 2
1 Criando Class Libraries com VB.NET
1 Importação de arquivos XML com SQL Server 2000
1 Web Services e AD/DBA
1 Criando Sequence com SQL Server
Quer receber novidades e e-books gratuitos?

Cursos Online

  • Banco de Dados
  • Carreira
  • Criação/Web
  • Excel/Projetos
  • Formação
  • + Todas as categorias
  • Contato: Telefone: (51) 3717-3796 | E-mail: webmaster@juliobattisti.com.br | Whatsapp: (51) 99627-3434

    Júlio Battisti Livros e Cursos Ltda | CNPJ: 08.916.484/0001-25 | Rua Vereador Ivo Cláudio Weigel, 537 - Universitário, Santa Cruz do Sul/RS, CEP: 96816-208

    Todos os direitos reservados, Júlio Battisti 2001-2019 ®

    [LIVRO]: MACROS E PROGRAMAÇÃO VBA NO EXCEL 2010 - PASSO-A-PASSO

    APRENDA COM JULIO BATTISTI - 1124 PÁGINAS: CLIQUE AQUI