What would be the way to validate the CPF checker digits in a DB using only a SELECT?

Question

Finding information on how to calculate the CPF check digits is not difficult. Any Google search gives numerous results. Most are reliable and even have a well respected source which is the entry on CPF on Wikipedia (where unfortunately some previously existing codes have been removed). Some of the algorithms presented there are in better shape than others.

There is even how to do this in PL/SQL. But the doubt would be how to calculate the digits and get whether this calculation is valid or invalid using only the command SELECT SQL, preferably with ANSI syntax or as close as possible. But the most important thing is the algorithm itself, more than a specific technical aspect.

The algorithm in this form is useful when you don’t have access to a database system with Stored Procedures or privileges to create an SP in a database.

If it helps, initially the data format contains no separator signals.

This is a scenario where it is not known whether the data contained in the database is reliable or not. Assemble a SELECT in an algorithm, which is usually done using more complex code, as demonstrated in Wikipedia, is not something so trivial.

Alternatives that help solve the fashion problem correct will be appreciated. Obviously there can be false positive or negative, which would destroy the purpose.

I do not know where there is official documentation on the subject. I have searched enough and did not find, at least in the obvious places. It’s amazing how lacking official source in all the places where you teach how to do the calculation. But it is something well known in Brazil.

No matter the check the cadastral situation in the IRS, only if the digits are correct.

Answer 1

Query for CPF digit calculation and validation

I used Mysql as a base, but it’s relatively simple to adapt to other "dialects".

Format-free base version, default

SELECT id, nome, cpf, CONCAT( @dig1:=(
      SUBSTR(cpf,1,1)   + SUBSTR(cpf,2,1)*2 + SUBSTR(cpf,3,1)*3+
      SUBSTR(cpf,4,1)*4 + SUBSTR(cpf,5,1)*5 + SUBSTR(cpf,6,1)*6+
      SUBSTR(cpf,7,1)*7 + SUBSTR(cpf,8,1)*8 + SUBSTR(cpf,9,1)*9 ) % 11 % 10
   ,(
      SUBSTR(cpf,2,1)   + SUBSTR(cpf,3,1)*2 + SUBSTR(cpf,4,1)*3 + 
      SUBSTR(cpf,5,1)*4 + SUBSTR(cpf,6,1)*5 + SUBSTR(cpf,7,1)*6 + 
      SUBSTR(cpf,8,1)*7 + SUBSTR(cpf,9,1)*8 + @dig1          *9 ) % 11 % 10
   ) AS digito FROM cadastro;

Version for the formatted CPF, in standard 000,000,000-00

This query already considers a stored CPF as groups of 3 digits separated by point and trace in the traditional way (or any other separator, provided it is one between each group). Basically the input values of the SUBSTR.

SELECT id, nome, cpf, CONCAT( @dig1:=(
      SUBSTR(cpf, 1,1)   + SUBSTR(cpf, 2,1)*2 + SUBSTR(cpf, 3,1)*3 +
      SUBSTR(cpf, 5,1)*4 + SUBSTR(cpf, 6,1)*5 + SUBSTR(cpf, 7,1)*6 +
      SUBSTR(cpf, 9,1)*7 + SUBSTR(cpf,10,1)*8 + SUBSTR(cpf,11,1)*9 ) % 11 % 10
   ,(
      SUBSTR(cpf, 2,1)   + SUBSTR(cpf, 3,1)*2 + SUBSTR(cpf, 5,1)*3 +
      SUBSTR(cpf, 6,1)*4 + SUBSTR(cpf, 7,1)*5 + SUBSTR(cpf, 9,1)*6 +
      SUBSTR(cpf,10,1)*7 + SUBSTR(cpf,11,1)*8 + @dig1           *9 ) % 11 % 10
   ) AS digito FROM cadastro;

Click here to see a functional test on SQL Fiddle.
_{(the numbers were generated by an online third party tool, and in two of them I purposely put errors)}

If the idea is just to identify valid Cpfs or not, simply move the formula to the clause WHERE:

-- Retorna os registros com CPF inválido:
SELECT id, nome, cpf FROM cadastro WHERE SUBSTR(cpf,13,2) != CONCAT( @dig1:=(
   ... o resto permanece igual ...
   );

For the format 00000000000, just adjust the IF( SUBSTR(cpf,13,2)... for IF( SUBSTR(cpf,10,2)..., and copy the right part of the above blocks.

Additionally, after checking the digits, you can find the invalid Cpfs with valid digit, from 000,000,000-00 to 999,999,999-99. But this really deserves a separate query.

Answer 2

I use the script below in Sqlserver for validation of a CPF. You can create a function and call it select

CREATE FUNCTION CPF_VALIDO(@CPF VARCHAR(11))
RETURNS CHAR(1)
AS
BEGIN
   DECLARE @INDICE INT,
               @SOMA INT,
               @DIG1 INT,
               @DIG2 INT,
               @CPF_TEMP VARCHAR(11),
               @DIGITOS_IGUAIS CHAR(1),
               @RESULTADO CHAR(1)

   SET @RESULTADO = 'N'

   /*
         Verificando se os digitos são iguais
         A Principio CPF com todos o números iguais são Inválidos
         apesar de validar o Calculo do digito verificado
         EX: O CPF 00000000000 é inválido, mas pelo calculo
         Validaria
   */

   SET @CPF_TEMP = SUBSTRING(@CPF,1,1)

   SET @INDICE = 1
   SET @DIGITOS_IGUAIS = 'S'

   WHILE (@INDICE <= 11)
   BEGIN
      IF SUBSTRING(@CPF,@INDICE,1) <> @CPF_TEMP
         SET @DIGITOS_IGUAIS = 'N'
      SET @INDICE = @INDICE + 1
   END;

   --Caso os digitos não sejão todos iguais Começo o calculo do digitos
   IF @DIGITOS_IGUAIS = 'N' 
   BEGIN
      --Cálculo do 1º dígito
      SET @SOMA = 0
      SET @INDICE = 1
      WHILE (@INDICE <= 9)
      BEGIN
         SET @Soma = @Soma + CONVERT(INT,SUBSTRING(@CPF,@INDICE,1)) * (11 - @INDICE);
         SET @INDICE = @INDICE + 1
      END

      SET @DIG1 = 11 - (@SOMA % 11)

      IF @DIG1 > 9
         SET @DIG1 = 0;

      -- Cálculo do 2º dígito }
      SET @SOMA = 0
      SET @INDICE = 1
      WHILE (@INDICE <= 10)
      BEGIN
         SET @Soma = @Soma + CONVERT(INT,SUBSTRING(@CPF,@INDICE,1)) * (12 - @INDICE);
         SET @INDICE = @INDICE + 1
      END

      SET @DIG2 = 11 - (@SOMA % 11)

      IF @DIG2 > 9
         SET @DIG2 = 0;

      -- Validando
      IF (@DIG1 = SUBSTRING(@CPF,LEN(@CPF)-1,1)) AND (@DIG2 = SUBSTRING(@CPF,LEN(@CPF),1))
         SET @RESULTADO = 'S'
      ELSE
         SET @RESULTADO = 'N'
   END
   RETURN @RESULTADO
END