Boa noite pessoas!
Como prometi na semana passada, hoje estarei demonstrando algumas formas de formatar valores numéricos e aplicar máscaras para dados como CNPJ e CPF. Antes de tudo, quero que vocês levem em consideração algumas recomendações importantes (do Gustavo Maia Aguiar):
“Não acho muito prudente que isso seja feito no banco de dados. Normalmente a aplicação possui diversas funções de formatação muito mais poderosas do que qualquer SGBD será. Isso é natural, pois, o SGBD tem como premissas básicas gravar e recuperar dados. A formatação fica por conta da apresentação e não por conta do banco.
Vejamos a simples conversão de um INT (4 bytes) para um número com 5 casas decimais. Um número como 1000 (INT – 4bytes) passará para um CHAR de 10 posições 1000.00000. Isso significa mais ciclos de CPU para fazer a conversão, mais memória para armazenar e mais rede para trafegar (6 bytes a mais).
Parece bobo e inocente, mas multiplique a utilização da formatação no banco de dados para todas as consultas e sistemas e aí sim as coisas começam a ficar mais perceptíveis.
Por essas e outras, se possível eu colocaria a formatação na aplicação e não no banco de dados.”
(Gustavo Maia Aguiar, http://social.msdn.microsoft.com/Forums/pt-BR/520/thread/243f12e7-75c8-483e-b3b3-afe898d32ef0)
Infelizmente não é recomendado criar rotinas de conversão e máscaras no banco de dado quando há a possibilidade de realizá-las na aplicação, mas sabemos que nem sempre podemos seguir estas recomendações, principalmente quando o banco de dados não possui entre a aplicação uma camada (WebService ou DLL) para aplicar questões de máscara, validações, conversões de dados e regras de negócio, tornando necessário centralizar estas rotinas dentro do banco de dados.
Feitas as recomendações, uma forma para aplicar um número específico de casas decimais para valores numéricos é a conversão para DECIMAL, definindo a escala e a precisão utilizada, exemplo:
-- Duas casas após a vírgula
SELECT CAST(89 AS DECIMAL(4, 2))
-- Três casas após a vírgula
SELECT CAST(89 AS DECIMAL(5, 3))
-- Duas casas após a vírgula, já no formato de texto
SELECT CAST(CAST(89 AS DECIMAL(4, 2)) AS CHAR)
-- Três casas após a vírgula, já no formato de texto
SELECT CAST(CAST(89 AS DECIMAL(5, 3)) AS CHAR)
Não se esqueça de definir a escala e a precisão do tipo decimal utilizado de acordo com o valor esperado, mesmo que um tipo “DECIMAL(9,2)” já dê conta de boa parte das situações, mais detalhes: http://msdn.microsoft.com/en-us/library/ms187746.aspx
Antes de tratar sobre como formatar CPF e CNPJ, vamos falar um pouco sobre a modelagem. Uma boa recomendação é o uso informações de CNPJ e CPF em formato numérico no banco de dados e sem máscara e se possível em um tipo de dado numérico, para evita utilizar memória desnecessária no armazenamento, no processamento e na comunicação com a aplicação. Para seguir esta recomendação, já vi sistemas utilizarem de tipos decimais (com precisão e escala para definir o tamanho do campo) e flutuantes (float, real), mas para estes tipos têm respectivamente as seguintes desvantagens utilizam espaço desnecessário e não permitem consistência adequada deste tipo de informação, desta forma para campos de CPF e CNPJ recomendo o uso de inteiros (bigint), assim como para outros códigos numéricos, mas sempre verifique o range dos tipos que você irá utilizar (http://msdn.microsoft.com/pt-br/library/ms187745.aspx).
Para armazenar CPF e CNPJ em um único campo, dois tipos de modelagem eu já tive a oportunidade de ver ser utilizada, a primeira com um campo adicional (boleando, bit) para identificar pessoa jurídica/física, que também era utilizado para questões de validar os campos de RG ou IE/IM. Em outra situação observei o uso de CPF com range negativo e CNPJ com range positivo.
Mas independente da modelagem que você utilize, sempre deixe estas definições bem claras aos outros profissionais envolvidos.
Quando utilizamos tipos inteiros para CPF e CNPJ, teremos o problema dos dados que começam com 0 (ex.: 000.094.093-84, que será salvo como 9409384), o que não permite utilizar uma formatação mais simples como:
DECLARE @CPF CHAR(11)
SET @CPF = 9409384
SELECT SUBSTRING(@CPF,1,3) + '.' + SUBSTRING(@CPF,4,3) + '.' + SUBSTRING(@CPF,7,3) + '-' + SUBSTRING(@CPF,10,2) -- #ERRO
A melhor forma seria enviar para a camada de aplicação tratar este valor, mas como algumas vezes não é possível, uma rotina que já supri esta necessidade é:
-- Para formatar CPF:
DECLARE @CPF VARCHAR(11)
SET @CPF = 9409384
SET @CPF = REPLICATE(0, 11 - LEN(@CPF)) + @CPF
SELECT SUBSTRING(@CPF,1,3) + '.'
+ SUBSTRING(@CPF,4,3) + '.'
+ SUBSTRING(@CPF,7,3) + '-'
+ SUBSTRING(@CPF,10,2)
-- Para formatar CNPJ:
DECLARE @CNPJ VARCHAR(14)
SET @CNPJ = 9409310084
SET @CNPJ = REPLICATE(0, 14 - LEN(@CNPJ)) + @CNPJ
SELECT SUBSTRING(@CNPJ,1,2) + '.'
+ SUBSTRING(@CNPJ,3,3) + '.'
+ SUBSTRING(@CNPJ,6,3) + '/'
+ SUBSTRING(@CNPJ,9,4) + '-'
+ SUBSTRING(@CNPJ,13,2)
Também desenvolvi uma rotina genérica que pode aplicar mascaras em valores numéricos, conforme abaixo:
DECLARE @VALOR BIGINT
, @MASCARA VARCHAR(40)
, @RESULTADO VARCHAR(40)
, @VALOR_TMP VARCHAR(40)
SELECT @VALOR = 9409384,
@MASCARA = '999.999.999-99',
@VALOR_TMP = @VALOR
;WITH SPLIT_MASCARA AS
(
SELECT 1 AS ID, SUBSTRING(@MASCARA, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@MASCARA, ID + 1, 1)
FROM SPLIT_MASCARA
WHERE ID < LEN(@MASCARA)
)
, SPLIT_VALOR AS
(
SELECT 1 AS ID, SUBSTRING(@VALOR_TMP, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@VALOR_TMP, ID + 1, 1)
FROM SPLIT_VALOR
WHERE ID < LEN(@VALOR_TMP)
)
, TRATAMENTO_MASCARA AS
(
SELECT
ID,
CASE WHEN LETRA = '9'
THEN '0'
ELSE LETRA
END AS LETRA,
CASE WHEN LETRA = '9'
THEN ROW_NUMBER() OVER (PARTITION BY LETRA ORDER BY ID)
ELSE 0
END AS ID_TMP
FROM SPLIT_MASCARA
)
, TRATAMENTO_VALOR AS
(
SELECT
(SELECT COUNT(*) FROM SPLIT_MASCARA WHERE LETRA = '9')
- (SELECT COUNT(*) FROM SPLIT_VALOR)
+ ID
AS ID_TMP,
LETRA
FROM SPLIT_VALOR
)
, RESULTADO AS
(
SELECT ID, ISNULL(V.LETRA, M.LETRA) AS LETRA
FROM TRATAMENTO_MASCARA M
LEFT JOIN TRATAMENTO_VALOR V
ON V.ID_TMP = M.ID_TMP
)
SELECT @RESULTADO = (SELECT LETRA + '' FROM RESULTADO ORDER BY ID FOR XML PATH(''))
PRINT @RESULTADO
Na verdade esta última rotina fiz mais como um desafio para evitar falar de SQL-CLR e ‘brincar’ com CTE. Pois em C# ou VB.NET seria um “pulo” para desenvolver uma rotina para aplicar máscaras. Mas… CTE Rocks!!!
Então pessoal, só para revisar:
- Formatações, validações e regras de negócio (se possível) somente na aplicação ou em uma camada intermediária (WebService ou DLL).
- Somente armazene dados numéricos com máscara quando for realmente necessário.
- Lembre de sempre validar as informações sobre a alocação de memória para os tipos de dados utilizados, evitando problemas de “overflow”.
Espero que tenham gostado dos exemplos e as dicas que eu trouxe até vocês. Na próxima semana volto a tratar questões de configurações do SQL Server, então até lá!
Artigos relacionados:
FUNÇÕES: Validação de CNPJ e CPF:
http://sqlfromhell.wordpress.com/2009/11/19/validacao-de-cnpj-e-cpf/
FUNÇÕES: Somente Números (com CTE)
http://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/
FUNÇÕES: Somente Números (com WHILE)
http://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/
Curtir isso:
Curtir Carregando...
Já faz um bom tempo que sou revisor dos artigos da revista Mundo.NET junto com Eduardo Ordine, Marcio Gomes, Zavaschi e Daniel Oliveira. E quando o pessoal da revista lembra-se da minha existência, me mandam um exemplar (valeu Carol!!!). Na semana passada recebi a revista número 21, que trouxe matérias interessantes sobre Windows 7 e Windows Phone 7 e artigos sobre Framework .NET 4 e Azure.
