Formatando valores numéricos, CPF e CNPJ

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:
https://sqlfromhell.wordpress.com/2009/11/19/validacao-de-cnpj-e-cpf/

FUNÇÕES: Somente Números (com CTE)
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/

FUNÇÕES: Somente Números (com WHILE)
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

2 pensamentos sobre “Formatando valores numéricos, CPF e CNPJ

  1. Uma solução alternativa.

    alter function fn_formatar_texto (
    @str varchar(max),
    @mask varchar(max) )
    RETURNS VARCHAR(max)
    as
    — select dbo.fn_formatar_texto(‘10947243000195′,’__.___.___/____-__’)
    begin
    declare @str_fim varchar(max),
    @idx int,
    @len int

    if @mask = ‘CNPJ’ set @mask = ‘__.___.___/____-__’
    else if @mask = ‘INSC_ESTADUAL’ set @mask = ‘___________-__’
    else if @mask = ‘CPF’ set @mask = ‘___.___.___-__’
    else if @mask = ‘CEP’ set @mask = ‘__.___-___’

    set @str_fim = ”

    set @len = len(@mask)
    set @idx = 1

    while @idx <= @len
    begin
    if SUBSTRING(@mask, @idx, 1) = '_'
    begin
    set @str_fim = @str_fim + SUBSTRING(@str, 1, 1)
    set @str = SUBSTRING(@str, 2, len(@str) – 1 )
    end
    else
    begin
    set @str_fim = @str_fim + SUBSTRING(@mask, @idx, 1)
    end

    set @idx = @idx + 1
    end

    return @str_fim
    end

    go
    select dbo.fn_formatar_texto ('10947243000195', 'CNPJ')

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s