Como validar CPF e CNPJ no SQL Server

Publicado em 29/08/2013 por Paulo R. Pereira

E ai pessoas, revisando o script de validação de CPF e CNPJ em T-SQL que adaptei em 2009, encontrei algumas melhorias pra deixá-lo mais limpo, espero que gostem:

DECLARE @TEXTO VARCHAR(20) = '02.841.834/0001-55'
 
DECLARE @CPF_CNPJ VARCHAR(20) = ''
 
;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)
 
SELECT @CPF_CNPJ += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)
 
IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
	SELECT 'Inválido'
	RETURN
END

DECLARE
	@I INT,
	@J INT = 1,
	@N INT = LEN(@CPF_CNPJ),
	@DIGITO1 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1),
	@DIGITO2 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1),
	@TOTAL_TMP INT,
	@COEFICIENTE_TMP INT,
	@DIGITO_TMP INT,
	@VALOR_TMP INT,
	@VALOR1 INT,
	@VALOR2 INT
 
WHILE @J <= 2
BEGIN
	SELECT
		@TOTAL_TMP = 0,
		@COEFICIENTE_TMP = 2,
		@I = @N + @J - 3
	
	WHILE @I >= 0
	BEGIN
		SELECT
			@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
			@TOTAL_TMP += @DIGITO_TMP * @COEFICIENTE_TMP,
			@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1,
			@I -= 1
 
		IF @COEFICIENTE_TMP > 9 AND @N = 14
			SET @COEFICIENTE_TMP = 2
	END
 
	SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
 
	IF (@VALOR_TMP >= 10)
		SET @VALOR_TMP = 0
 
	IF @J = 1
		SET @VALOR1 = @VALOR_TMP
	ELSE
		SET @VALOR2 = @VALOR_TMP

	SET @J += 1
END
 
SELECT
	CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
		THEN 'Válido'
		ELSE 'Inválido'
	END

E o script para criar uma função de validação de CPF e CNPJ:

CREATE FUNCTION [dbo].[UDF_ValidaCpfCnpj] (@TEXTO VARCHAR(20))
RETURNS BIT
AS 
BEGIN 
	DECLARE @CPF_CNPJ VARCHAR(20) = ''
 
	;WITH SPLIT AS
	(
		SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
		UNION ALL
		SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
		FROM SPLIT
		WHERE ID < LEN(@TEXTO)
	)
 
	SELECT @CPF_CNPJ += LETRA
	FROM SPLIT
	WHERE LETRA LIKE '[0-9]'
	OPTION(MAXRECURSION 0)
 
	IF LEN(@CPF_CNPJ) NOT IN (11, 14)
	BEGIN
		RETURN 0
	END

	DECLARE
		@I INT,
		@J INT = 1,
		@N INT = LEN(@CPF_CNPJ),
		@DIGITO1 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1),
		@DIGITO2 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1),
		@TOTAL_TMP INT,
		@COEFICIENTE_TMP INT,
		@DIGITO_TMP INT,
		@VALOR_TMP INT,
		@VALOR1 INT,
		@VALOR2 INT
 
	WHILE @J <= 2
	BEGIN
		SELECT
			@TOTAL_TMP = 0,
			@COEFICIENTE_TMP = 2,
			@I = @N + @J - 3
	
		WHILE @I >= 0
		BEGIN
			SELECT
				@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
				@TOTAL_TMP += @DIGITO_TMP * @COEFICIENTE_TMP,
				@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1,
				@I -= 1
 
			IF @COEFICIENTE_TMP > 9 AND @N = 14
				SET @COEFICIENTE_TMP = 2
		END
 
		SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
 
		IF (@VALOR_TMP >= 10)
			SET @VALOR_TMP = 0
 
		IF @J = 1
			SET @VALOR1 = @VALOR_TMP
		ELSE
			SET @VALOR2 = @VALOR_TMP

		SET @J += 1
	END
 
	RETURN
		CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
			THEN 1
			ELSE 0
		END
END    
GO

SELECT CASE 
	WHEN [dbo].[UDF_ValidaCpfCnpj] ('02.841.834/0001-55') = 1 
	THEN 'Válido' 
	ELSE 'Inválido' 
	END

E um segundo script para criar uma função de validação de CPF e CNPJ, considerando que o CPF ou CNPJ que a função vai receber nunca terá caracteres especiais ou mais/menos dígitos que um CPF ou CNPJ possui:

CREATE FUNCTION [dbo].[UDF_ValidaCpfCnpj] (@CPF_CNPJ VARCHAR(20))
RETURNS BIT
AS 
BEGIN 
	DECLARE
		@I INT,
		@J INT = 1,
		@N INT = LEN(@CPF_CNPJ),
		@DIGITO1 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1),
		@DIGITO2 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1),
		@TOTAL_TMP INT,
		@COEFICIENTE_TMP INT,
		@DIGITO_TMP INT,
		@VALOR_TMP INT,
		@VALOR1 INT,
		@VALOR2 INT
 
	WHILE @J <= 2
	BEGIN
		SELECT
			@TOTAL_TMP = 0,
			@COEFICIENTE_TMP = 2,
			@I = @N + @J - 3
	
		WHILE @I >= 0
		BEGIN
			SELECT
				@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
				@TOTAL_TMP += @DIGITO_TMP * @COEFICIENTE_TMP,
				@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1,
				@I -= 1
 
			IF @COEFICIENTE_TMP > 9 AND @N = 14
				SET @COEFICIENTE_TMP = 2
		END
 
		SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
 
		IF (@VALOR_TMP >= 10)
			SET @VALOR_TMP = 0
 
		IF @J = 1
			SET @VALOR1 = @VALOR_TMP
		ELSE
			SET @VALOR2 = @VALOR_TMP

		SET @J += 1
	END
 
	RETURN
		CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
			THEN 1
			ELSE 0
		END
END    
GO

SELECT 
	CASE WHEN [dbo].[UDF_ValidaCpfCnpj] ('02841834000155') = 1 
	THEN 'Válido' 
	ELSE 'Inválido' 
	END

Quem tiver alguma dica ou sugestão pra melhorar este script, fique a vontade para deixar seu comentário.

Artigos relacionados:

FUNÇÕES: Validação de CNPJ e CPF com T-SQL

FUNÇÕES: Somente Números (com CTE)

FUNÇÕES: Somente Números (com WHILE)

Verificar a versão dos dados de uma tabela por CHECKSUM

Publicado em 26/07/2012 por Paulo R. Pereira

Olá pessoas!

Recentemente, encontrei um script bem interessante para realizar o CHECKSUM de todos os dados de todas as colunas de uma tabela, fiz algumas alterações mínimas para reduzir o código do script original, e ficou desta forma:

DECLARE @schema_name sysname
SET @schema_name = 'dbo'

DECLARE @table_name sysname
SET @table_name = 'Nome da tabela'

DECLARE @column_list VARCHAR(MAX)

SELECT @column_list =
	COALESCE(@column_list + ', ', '') + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND TABLE_SCHEMA = @schema_name

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT CHECKSUM_AGG(CHECKSUM({@column_list})) FROM [{@schema_name}].[{@table_name}]'

SET @sql = REPLACE(@sql, '{@column_list}', @column_list)
SET @sql = REPLACE(@sql, '{@schema_name}', @schema_name)
SET @sql = REPLACE(@sql, '{@table_name}', @table_name)

EXEC (@sql)

Esta consulta retorna o CHECKSUM de todos os dados da tabela de forma rápida (na maioria das vezes), e permite verificar por meio deste CHECKSUM se a tabela foi ou não alterada, sendo uma ótima alternativa comparar alterações em tabelas similares em bancos distintos e criar uma estrutura simples de versionamento.

A única limitação que encontrei para este script é a necessidade de um tratamento diferenciado para colunas XML:

Msg 8116, Level 16, State 4, Line 1
Argument data type xml is invalid for argument X of checksum function.

Referências

Script original:
http://stackoverflow.com/questions/1560306/calculate-hash-or-checksum-for-a-table-in-sql-server

Função CHECKSUM:
http://msdn.microsoft.com/en-us/library/ms189788.aspx

Função CHECKSUM_AGG:
http://msdn.microsoft.com/en-us/library/ms188920.aspx

Artigos relacionados:

Comparando a estrutura de tabelas diferentes
https://sqlfromhell.wordpress.com/2010/01/09/query-from-hell-comparando-a-estrutura-de-tabelas-diferentes/

Red Gate – Overview – SQL Data Compare
https://sqlfromhell.wordpress.com/2011/05/23/red-gate-overview-sql-data-compare/

Visual Studio 2010 – Data Compare
https://sqlfromhell.wordpress.com/2011/01/27/visual-studio-2010-data-compare/

FUNÇÕES: Somente Números (com CTE) Versão 2

Publicado em 18/03/2010 por Paulo R. Pereira

[2011-04-02] Código completo – Somente números:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo – Somente texto:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo – Remover caracteres especiais:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[Post-Original] Explicação:

Boa noite pessoas!

Já faz algum tempo que escrevi as funções “somente números” com WHILE e CTE, até que estes dias eu tive de reformular elas de forma a remover caracteres especiais, remover máscaras e afins, assim como para outras funcionalidades mais absurdas… O legal foi que causou uma leve mudança da estrutura original da minha CTE antiga, resultando numa terceira função “somente números” bem interessante, que facilmente pode ser tranformada em “somente letras”, “somente números e letras”, “somente caractéres internacionais”… Ok, famos ao que interessa!

Primeiramente temos o texto:

DECLARE @Texto NVARCHAR(MAX)
SET @Texto = N'O azul é uma das três cores-luz primárias,
e cor-pigmento secundária, resultado da sobreposição dos
pigmentos ciano e magenta. Seu comprimento de onda é da
ordem de 455 a 492 nanômetros do espectro de cores visíveis.';

Em seguida uma variável que receberá o resultado:

DECLARE @Result NVARCHAR(MAX)
SET @Result = ''

E a expressão/CTE de split de caractere por caractere:

;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@Texto, 1, 1) AS CH
UNION ALL
SELECT ID + 1, SUBSTRING(@Texto, ID + 1, 1)
FROM SPLIT
WHERE ID < LEN(@Texto)
)

E agora para filtrar e juntar todos os caracteres:

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[0-9]'
OPTION (MAXRECURSION 0)

Enfim exibindo o resultado:

SELECT @Result AS [Resultado]

Também é possível adaptar a consulta para “somente letras” ou “somente alfa-numéricos” (removendo os caracteres especiais):

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z]'
OPTION (MAXRECURSION 0)

-- OU

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z0-9]'
OPTION (MAXRECURSION 0)

Mas estas consultas também podem ter mais utilidade com um CASE substituindo os caracteres especiais por espaço ou outro caractere qualquer:

SELECT @Result = @Result
+ (CASE WHEN CH LIKE '[A-z0-9]' THEN CH ELSE '' END)
FROM SPLIT
OPTION (MAXRECURSION 0)

Mas sempre é bom lembrar que é melhor ter rotinas deste gênero na aplicação no lugar do banco de dados ou pensar em CLR.

Obrigado Eduardo Ruthes pela idéia inicial desta CTE!!!

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) [Original]
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/

Formatando valores numéricos, CPF e CNPJ

Publicado em 28/11/2009 por Paulo R. Pereira

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/

FUNÇÕES: Validação de CNPJ e CPF com T-SQL

Publicado em 19/11/2009 por Paulo R. Pereira

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(20)
SET @TEXTO = '02.841.834/0001-55'

DECLARE @CPF_CNPJ VARCHAR(20)
SET @CPF_CNPJ = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @CPF_CNPJ += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)

IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
	SELECT 'Inválido'
	RETURN
END

DECLARE
	@DIGITO1 INT,
	@DIGITO2 INT,
	@VALOR1 INT,
	@VALOR2 INT

DECLARE
	@I INT,
	@J INT,
	@TOTAL_TMP INT,
	@COEFICIENTE_TMP INT,
	@DIGITO_TMP INT,
	@VALOR_TMP INT

SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)
SET @J = 1

WHILE @J <= 2 BEGIN 	SELECT 		@TOTAL_TMP = 0, 		@COEFICIENTE_TMP = 2 	SET @I = ((LEN(@CPF_CNPJ) - 3) + @J) 	WHILE @I >= 0
	BEGIN
		SELECT
			@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
			@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
			@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1

		IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
			SET @COEFICIENTE_TMP = 2
		SET @I = @I - 1
	END

	SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)

	IF (@VALOR_TMP >= 10)
		SET @VALOR_TMP = 0

	IF @J = 1
		SET @VALOR1 = @VALOR_TMP
	ELSE
		SET @VALOR2 = @VALOR_TMP
	SET @J = @J + 1
END

SELECT
	CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
		THEN 'Válido'
		ELSE 'Inválido'
	END

[Post-Original] Explicação:

Boa noite pessoas!

No blog do Rogério, encontrei um ótimo algoritmo de validação de CNPJ e CPF para ORACLE, para que ninguém tenha que ficar transitando de ‘PL\SQL’ para ‘T-SQL’, já realizei este trabalho e vou detalhar um pouco o funcionamento deste algoritmo.

Primeiro precisamos de um CNPJ ou CPF para validar, assim identifiquei um que estava “vagando” na internet.

DECLARE @CPF_CNPJ VARCHAR(20)
SET @CPF_CNPJ = '02.841.834/0001-55'

Ok, tenho o CNPJ/CPF para validar, mas tenho que remover os caracteres não numéricos, desta forma utilizarei a função SOMENTE_NUMEROS que desenvolvi para o artigo anterior:

SET @CPF_CNPJ = dbo.SOMENTE_NUMEROS(@CPF_CNPJ)

A primeira valiação verifica se a quantidade de caracteres é de um CPF ou de um CNPJ

IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
SELECT 'Inválido'
RETURN
END

Em sequida, defino quatro variáveis que serão utilizadas para a validação dos digitos verificadores do CNPJ e CPF.

DECLARE
@DIGITO1 INT,
@DIGITO2 INT,
@VALOR1 INT,
@VALOR2 INT

Também há outras variáveis a serem utilizadas durante a operação.

DECLARE
@I INT,
@J INT,
@TOTAL_TMP INT,
@COEFICIENTE_TMP INT,
@DIGITO_TMP INT,
@VALOR_TMP INT

As variáveis DIGITO1 e DIGITO2 armazenarão os digitos verificadores do CNPJ/CPF.

SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)

Feito isso, determino um laço de repetição para dois loops, um para cada digito.

SET @J = 1
WHILE @J <= 2
BEGIN

Determino os valores inicias das variáveis de validação.

SELECT
@TOTAL_TMP = 0,
@COEFICIENTE_TMP = 2

Agora, aplico o algoritmo para gerar cada um dos digitos verificadores.

SET @I = ((LEN(@CPF_CNPJ) - 3) + @J)
WHILE @I >= 0
BEGIN
SELECT
@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1
IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
SET @COEFICIENTE_TMP = 2
SET @I = @I - 1
END
 SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
IF (@VALOR_TMP >= 10)
SET @VALOR_TMP = 0

Identifico qual dos digitos verificadores foi gerado e prossigo para o próximo digito no laço de repetição.

IF @J = 1
SET @VALOR1 = @VALOR_TMP
ELSE
SET @VALOR2 = @VALOR_TMP
SET @J = @J + 1
END

Por fim, verifico se os digitos gerados têm o mesmo valor dos presente no CNPJ/CPF.

IF @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
SELECT 'Válido'
ELSE
SELECT 'Inválido'

Para o próximo artigo, ainda estou estudando uma forma interessante de trabalhar com máscaras e CTE.

Artigos relacionados:

Blog do Rogério – Validação CNPJ/CPF com PL/SQL
http://blogdozunga.blogspot.com/2009/02/precisava-de-uma-rotina-para-validar.html

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/

FUNÇÕES: Somente Números (com CTE)

Publicado em 18/11/2009 por Paulo R. Pereira

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE
	@RESULTADO VARCHAR (8000),
	@ZERO INT,
	@NOVE INT

SELECT
   @ZERO = UNICODE(0),
   @NOVE = UNICODE(9)

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)
, NUMEROS AS
(
	SELECT LETRA
	FROM SPLIT
	WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo v2:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[Post-Original] Explicação:

Boa noite pessoal!

Continuando o artigo anterior, vou demonstrar como recuperar somente os valores numéricos de um texto, agora com CTE.

Para começar, tenho o mesmo cenário do artigo anterior, uma variável com um texto absurdo, da qual desejo somente retornar os valores numéricos.

DECLARE @PALAVRA VARCHAR(8000)
SET @PALAVRA = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'
</code></div>
Como estou utilizando CTE, não tenho necessidade de muitas variáveis.
<div style="border: 1px; background-color: #000000; color: #ffffff; font-weight: bold; border-color: #ffffff; margin: 5px; padding: 5px;"><code>DECLARE
@RESULTADO VARCHAR (8000),
@ZERO INT,
@NOVE INT

Mas ainda continuo a ter a necessidade das variáveis que delimitam o RANGE dos valores numéricos, no caso os ‘valores decimais’ dos caracteres 0 (zero) e 9 (nove):

SELECT
@ZERO = UNICODE(0),
@NOVE = UNICODE(9)

Com a primeira CTE, realizo crio a relação de um a um dos caracteres do meu texto (um caractere por linha). Na segunda CTE, aplico a validação do RANGE de cada um dos caracteres relacionados na primeira CTE.

;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@PALAVRA, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@PALAVRA, ID + 1, 1)
FROM SPLIT
WHERE ID &lt; LEN(@PALAVRA)
)
, NUMEROS AS
(
SELECT LETRA
FROM SPLIT
WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

Por fim, utilizo uma query para concatenar os valores resultantes da segunda CTE, adicionando o HINT MAXRECURSION, para evitar que um texto com mais de 100 caracteres prejudique minha operação.

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 8000)

No próximo artigo trarei a validação de CPF e CNPJ em uma única operação (e de forma otimizada).

Artigos relacionados:

CTE – Introdução:
https://sqlfromhell.wordpress.com/2009/09/20/cte-introducao/

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

FUNÇÕES: Somente Números (com WHILE)

Publicado em 17/11/2009 por Paulo R. Pereira

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE
	@RESULTADO VARCHAR (8000),
	@LETRA CHAR(1),
	@NUMERO INT,
	@CONT INT,
	@ZERO INT,
	@NOVE INT

SELECT
	@RESULTADO = '',
	@CONT = 0,
	@ZERO = UNICODE(0),
	@NOVE = UNICODE(9),
	@NUMERO = LEN(@TEXTO)

WHILE @CONT < @NUMERO
BEGIN
	SET @CONT = @CONT + 1
	SET @LETRA = SUBSTRING(@TEXTO, @CONT, 1)

	IF UNICODE(@LETRA) BETWEEN @ZERO AND @NOVE
	BEGIN
		SET @RESULTADO = @RESULTADO + @LETRA
	END
END

SELECT @RESULTADO

[2011-04-02] Código completo v2:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE
	@RESULTADO VARCHAR (8000),
	@LETRA CHAR(1),
	@NUMERO INT,
	@CONT INT

SELECT
	@RESULTADO = '',
	@CONT = 0,
	@NUMERO = LEN(@TEXTO)

WHILE @CONT < @NUMERO
BEGIN
	SET @CONT = @CONT + 1
	SET @LETRA = SUBSTRING(@TEXTO, @CONT, 1)

	IF @LETRA LIKE '[0-9]'
	BEGIN
		SET @RESULTADO = @RESULTADO + @LETRA
	END
END

SELECT @RESULTADO

[Post-Original] Explicação:

Boa noite pessoal!

Para filtrar um texto, deixando somente os valores numéricos, pensei em realizar de duas formas, a primeira com WHILE e outra com CTE, mas como CTE se torna um pouco complexo para alguns, vou começar com WHILE:

Primeiro tenho meu texto, no caso estou utilizando a variável @PALAVRAS com um texto que encontrei na internet.

DECLARE @PALAVRA VARCHAR(8000)
SET @PALAVRA = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

Tendo o texto, vou definir quais as variáveis que vou utilizar, assim tenho uma variável onde salvarei o resultado (@RESULTADO) da operação, uma variável para armazenar uma a uma as ‘letras’ (@LETRA) da operação, uma variável para saber quantas ‘letras’ tenho no texto (@NUMERO) e outra para controle dos ‘loops’ da operação (@CONT).

DECLARE
@RESULTADO VARCHAR (8000),
@LETRA CHAR(1),
@NUMERO INT,
@CONT INT,
@ZERO INT,
@NOVE INT

Esqueci de dizem que é @ZERO e @NOVE, bem… elas irão armazenar os valores que identifica range inicial e final dos valores numéricos, no caso 0 (zero) e o 9 (nove) respectivamente. A função UNICODE retorna o ‘valor decimal’ de um determinado caractere.

Uma observação importante é o valor inicial de @RESULTADO, pois como o valor inicial das variáveis é NULL, NULL somado a qualquer outros valor, nas configurações padrões do T-SQL, tem como resultado NULL, então precisamos estipular o valor inicial dele, assim como das outras variáveis.

SELECT
@RESULTADO = '',
@CONT = 0,
@ZERO = UNICODE(0),
@NOVE = UNICODE(9),
@NUMERO = LEN(@PALAVRA)

Vamos agora ao laço de repetição, que percorrerá uma a uma as letras.

WHILE @CONT < @NUMERO
BEGIN
SET @CONT = @CONT + 1
SET @LETRA = SUBSTRING(@PALAVRA, @CONT, 1)

Agora temos a validação, onde verifico se a variável @LETRA esta em 0 (zero) e 9 (nove). Utilizando novamente a função UNICODE, que retorna o ‘valor decimal’ de um caractere.

IF UNICODE(@LETRA) BETWEEN @ZERO AND @NOVE
BEGIN
SET @RESULTADO = @RESULTADO + @LETRA
END
END

Agora temos a primeira solução. No próximo artigo estarei detalhando a forma de realizar esta operação com CTE.

Artigos relacionados:

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

FUNÇÕES: Listas de valores numéricos

Publicado em 16/11/2009 por Paulo R. Pereira

Boa noite pessoal!

Estive pensando sobre algumas formas de ajudar vocês em necessidades do dia-dia, assim tive a idéia de nas próximas semanas trazer uma relação de queries que facilitam estas necessidades. Uma que eu sempre me deparo é gerar listas numéricas, assim elaborei este artigo para ajuda-los a nesta situação.

Então para uma lista de valores numéricos e tenho as seguintes alternativas:

1. Criar tabela temporária e inserir os registros um a um dentro de um WHILE? Inserir os registros um a um não será tão perfomático, mas se o fizer, não posso esquecer do NOCOUNT, para evitar as mensagens de ‘linhas afetadas’:

SET NOCOUNT ON;

2. Fazer uma consulta com vários UNIONs? Mas quantas linhas mesmo eu vou ter que digitar? Algo em torno de duas linhas para cada valor numérico? Para listas grandes… Nem pensar…

--Criando uma lista de 0-9 sem recursividade.

SELECT 0 AS ID

UNION ALL

  SELECT 1

UNION ALL

  SELECT 2

UNION ALL

  SELECT 3

UNION ALL

  SELECT 4

UNION ALL

  SELECT 5

UNION ALL

  SELECT 6

UNION ALL

  SELECT 7

UNION ALL

  SELECT 8

UNION ALL

  SELECT 9

3. Fazer uma CTE recursiva? Pode ser, a única desvantagem será algo próximo a 9,13 leituras por linha durante a execução do código e o limite de recursividade.

--Criando uma lista de 0-9 com recursividade.

;WITH Lista AS

(

  SELECT 0 AS ID

UNION ALL

  SELECT ID + 1

  FROM Lista

  WHERE ID < 9

)

SELECT * FROM Lista

4. Então vamos tornar o processo prático e deixar o código mais “limpo”, criarei uma função que retornara uma lista gerada com CTE, passando como argumentos o range a ser utilizado.

--Função

CREATE FUNCTION Lista(@inicial INT, @final INT)

RETURNS @Resultado TABLE (valor INT)

AS

BEGIN 

  IF @inicial <= @final

  BEGIN

    WITH CTE AS

    (

      SELECT @inicial AS valor

      UNION ALL

        SELECT valor + 1

        FROM CTE

        WHERE valor < @final

    )

    INSERT INTO @Resultado (valor)

      SELECT valor

      FROM CTE

      OPTION (MAXRECURSION 0)

  END

  ELSE 

  BEGIN

    WITH CTE AS

    (

      SELECT @inicial AS valor

      UNION ALL

        SELECT valor - 1

        FROM CTE

        WHERE valor > @final

    )

    INSERT INTO @Resultado (valor)

      SELECT valor

      FROM CTE

      OPTION (MAXRECURSION 0)

  END   

  RETURN;

END;

E as chamadas para os ranges variados.

SELECT valor FROM dbo.Lista(-10, 100000);

SELECT valor FROM dbo.Lista(10, -100000);

5. Mas no final a melhor alternativa é criar uma tabela no banco de dados com os valores que serão utilizados.

Se alguém tiver alguma outra alternativa, estou disposto a testar. Para o próximo artigo, estou pensando em funções para validação de valores numéricos.

Artigos relacionados:

CTE – Introdução:
https://sqlfromhell.wordpress.com/2009/09/20/cte-introducao/

WHILE vs CTE – Popular tabela de testes:
https://sqlfromhell.wordpress.com/2009/09/12/while-vs-cte-popular-tabela-de-testes/

Trabalhando com Datas – Lista de meses e dias da semana:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

SQL From Hell.com

Um nome bem intuitivo para muita informação!

Arquivo da categoria: Funções SQL

Como validar CPF e CNPJ no SQL Server

Verificar a versão dos dados de uma tabela por CHECKSUM

FUNÇÕES: Somente Números (com CTE) Versão 2

Formatando valores numéricos, CPF e CNPJ

FUNÇÕES: Validação de CNPJ e CPF com T-SQL

FUNÇÕES: Somente Números (com CTE)

FUNÇÕES: Somente Números (com WHILE)

FUNÇÕES: Listas de valores numéricos

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso: