Máscaras para CPF, CNPJ e outros tipos de dados com SQL

Máscaras para CPF, CNPJ e outros tipos de dados com SQL

Neste artigo vamos ver como trabalhar com máscaras no SQL. Os exemplos que vamos ver podem ser feitos no SQL Server ou no MySQL.

A aplicação de máscaras pode ser algo feito por nossa aplicação, mas, em alguns casos pode ser necessário aplicar máscaras a resultados durante os nossos relatórios. Por exemplo, você pode ter um banco de dados onde o CPF, CNPJ ou CEP estejam armazenados sem traços ou pontos. Para fins de exibição em algum relatório, acaba sendo necessário aplicar uma máscara nestas informações.

Para fins de testes, vamos criar a seguinte tabela

//SQL SERVER

CREATE TABLE [dbo].[clientes](

[codigo] [int] IDENTITY(1,1) NOT NULL,

[documento] [varchar](14) NULL,

[nome] [varchar](50) NULL,

 CONSTRAINT [PK_clientes] PRIMARY KEY CLUSTERED 

(

[codigo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

//MySQL

CREATE TABLE IF NOT EXISTS clientes (

  codigo int(11) NOT NULL,

  documento varchar(14) NOT NULL,

  nome varchar(50) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

ALTER TABLE clientes ADD PRIMARY KEY ('codigo');

 

Depois de criar a tabela, vamos inserir os seguintes registros de testes.

 

INSERT into clientes (documento, nome) VALUES ('57606441146', 'Teste 1');

INSERT into clientes (documento, nome) VALUES ('71645145000198', 'Teste 2');

INSERT into clientes (documento, nome) VALUES ('57142385910', 'Teste 3');

INSERT into clientes (documento, nome) VALUES ('76684147000100', 'Teste 4');

INSERT into clientes (documento, nome) VALUES ('28390677148', 'Teste 5');

INSERT into clientes (documento, nome) VALUES ('81144631000186', 'Teste 6');

 

Os números de CPF e CNPJ foram gerados de forma aleatória utilizando os seguintes sites:

 

https://www.geradorcpf.com

https://www.geradorcnpj.com

 

Para aplicar a máscara de CPF ou CNPJ em nossa tabela, temos que separar os números do campo documento. Tomando como base a tabela de clientes, o formato do CPF é ###.###.###-##. Para fazer isso, utilizamos a função SUBSTRING. Esta função recebe 3 parâmetros, o primeiro é a string que vamos pesquisar, o segundo é a posição inicial de nossa pesquisa, e a terceira é a quantidade de caracteres que desejamos retornar. Por exemplo, para retornar os 3 primeiros caracteres, poderiamos fazer desta forma

 

//SQL SERVER

SELECT documento, SUBSTRING(documento,1,3) AS resultado FROM clientes where LEN(documento) = 11

//MYSQL

SELECT documento, SUBSTRING(documento,1,3) AS resultado FROM clientes where LENGTH(documento) = 11

 

E se fosse necessário retornar os 2 últimos caracteres? Podemos fazer desta forma:

 

//SQL SERVER

SELECT documento, SUBSTRING(documento,10,2) AS resultado FROM clientes where LEN(documento) = 11

//MYSQL

SELECT documento, SUBSTRING(documento,10,2) AS resultado FROM clientes where LENGTH(documento) = 11

 

A função LEN (Sql Server) e LENGTH(MySQL) servem para retornar o tamanho em caracteres de um determinado campo. Como o nosso campo "documento" guarda tanto CPF quanto CNPJ, no exemplo acima estamos retornando registros com o tamanho de 11 caracteres, que é o CPF sem traços ou pontos.

 

//SQL SERVER ou Mysql

SELECT documento, LEFT(documento, 3) AS resultado FROM clientes;

SELECT documento, RIGHT(documento,2) AS resultado FROM clientes;

 

Uma observação. A função SUBSTRING nos permite selecionar a partir de qualquer posição desejada. Mas, nos exemplos acima (selecionar a partir do começo ou do fim do campo) existem duas funções que podem facilitar, que são as funções LEFT (esquerda) e RIGHT (direita). Elas podem selecionar uma determinada quantidade de caracteres. Os exemplos acima ficaria da seguinte forma:

Agora que sabemos como separar os números do campo, temos que aplicar a máscara. Para isso, vamos concatenar o conteúdo da máscara (pontos, traços) de acordo com o formato desejado. Para concatenar no SQL Server, podemos utilizar o "+", no MySQL, temos que utilizar a função CONCAT

 

//SQL SERVER

SELECT documento, SUBSTRING(documento,1,3) + '.'

+ SUBSTRING(documento,4,3) + '.'

+ SUBSTRING(documento,7,3) + '-'

+ SUBSTRING(documento,10,2) AS resultado FROM clientes where LEN(documento) = 11

 

//MYSQL

SELECT documento, CONCAT(SUBSTRING(documento,1,3),'.',SUBSTRING(documento,4,3),'.',SUBSTRING(documento,7,3),'-',SUBSTRING(documento,10,2)) AS resultado FROM clientes where LENGTH(documento) = 11

 

Para aplicar a máscara em CNPJ, basta seguir o mesmo conceito apresentado acima.

 

//SQL SERVER

SELECT documento, SUBSTRING(documento,1,2) + '.'

+ SUBSTRING(documento,3,3) + '.'

+ SUBSTRING(documento,6,3) + '/'

+ SUBSTRING(documento,9,4) + '-'

+ SUBSTRING(documento,13,2) AS resultado FROM clientes where LEN(documento) = 14

//MYSQL

SELECT documento,CONCAT(SUBSTRING(documento,1,2),'.',SUBSTRING(documento,3,3),'.',SUBSTRING(documento,6,3),'/'

,SUBSTRING(documento,9,4),'-',SUBSTRING(documento,13,2)) AS resultado FROM clientes where LENGTH(documento) = 14

 

Perceba que nos exemplos vistos até agora, sempre filtramos os resultados de acordo com o tamanho do campo (11 carateres para CPF e 14 caracteres para CNPJ). Seria interessante fazer a máscara sem precisar desta condição.

Para isso, vamos utilizar um CASE para determinar o tamanho do campo. De acordo com o tamanho do campo, vamos aplicar a máscara correspondente. O nosso código ficaria da seguinte forma:

//SQL SERVER

SELECT documento,

CASE WHEN LEN(documento)=11 THEN

SUBSTRING(documento,1,3) + '.'

+ SUBSTRING(documento,4,3) + '.'

+ SUBSTRING(documento,7,3) + '-'

+ SUBSTRING(documento,10,2)

ELSE

SUBSTRING(documento,1,2) + '.'

+ SUBSTRING(documento,3,3) + '.'

+ SUBSTRING(documento,6,3) + '/'

+ SUBSTRING(documento,9,4) + '-'

+ SUBSTRING(documento,13,2)

END

AS resultado FROM clientes

 

//MySQL

SELECT documento,

CASE WHEN LENGTH(documento)=11 THEN

CONCAT(SUBSTRING(documento,1,3),'.',SUBSTRING(documento,4,3),'.',SUBSTRING(documento,7,3),'-',SUBSTRING(documento,10,2))

ELSE

CONCAT(SUBSTRING(documento,1,2),'.',SUBSTRING(documento,3,3),'.',SUBSTRING(documento,6,3),'/'

,SUBSTRING(documento,9,4),'-',SUBSTRING(documento,13,2)) 

END

AS resultado FROM clientes

 

Encerramento

Neste artigo você viu como aplicar máscaras em registros utilizado SQL.

Como você viu, utilizamos muitas funçoes de manipulação de strings para chegar no resultado desejado. Se você quiser se aprofundar sobre este assunto, não deixe de consultar o artigo a seguir

 

Funções de manipulação de strings no SQL

 

Se você tem curiosidade em saber como funciona a lógica de validação do CPF, eu explico em detalhes na página a seguir como isso funciona.

Como funciona a lógica da validação do CPF?

 

Outros conteudos que podem ser de seu interesse

Inserir valores em campos do tipo autonumeração no SQL Server
26/09/2019SQL

Inserir valores em campos do tipo autonumeração no SQL Server

Aprenda como inserir valores em campos de autonumeração no SQL Server

Saiba mais...
Salários na área de TI
02/01/2022SQL

Salários na área de TI

Uma lista de tabelas de salários na área de Tecnologia da Informação

Saiba mais...

Conteúdo sobre banco de dados sem complicação!