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

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?