Find MySQL record by specific character

Navigation
2

I have a database named completo , whose dados table is: 

        id  cpf          nome
         1  38831370570  joao da silva
         2  27283620508  maria joaquina
         3  94470661945  carlos eduardo

I would like to 'filter' all CPFs that contain a given digit in the ninth position, based on this:

  

"To determine in which Brazilian state a CPF was issued, it is necessary to obtain the last digit of the CPF, ignoring the two digits of control / verifiers An example of how to determine this number is the CPF number XXX.XXX.XX6- XX We highlight the last valid CPF number, ignoring the verifying digits. "

I want to search the bank for all CPFs in Rio de Janeiro, for example, according to this relation: 

 1. Distrito Federal, Goiás, Mato Grosso do Sul e Tocantins;
 2. Pará, Amazonas, Acre, Amapá, Rondônia e Roraima;
 3. Ceará, Maranhão e Piauí;
 4. Pernambuco, Rio Grande do Norte, Paraíba e Alagoas;
 5. Bahia e Sergipe;
 6. Minas Gerais;
 7. Rio de Janeiro e Espírito Santo;
 8. São Paulo;
 9. Paraná e Santa Catarina;
 10.Rio Grande do Sul.
    
asked by anonymous 21.06.2017 / 00:49

2 answers

4

You will use SUBSTRING , which returns a specific number of characters.

In the example below I add the SUBSTRING(cpf,9,1) , which will return the ninth character and compare with the desired state number: 

SELECT * FROM dados WHERE SUBSTRING(cpf,9,1) = '8'

In the example above, the CPFs of São Paulo will be returned. You change the number 8 by the number of the state you want.

    
21.06.2017 / 01:19
1

You can use the MID function that returns one specified number of characters in a string, given an initial position and length. 

 SELECT * FROM dados where MID(cpf,9,1) = 7;

List with 8 sample CPFs

Queryresult,showingonlyCPFswhose9thpositionequals7:

In SQL Fiddle

On my server:

Anotheroptionis substr SELECT * FROM dados where substr(cpf,-3,1) = 7; SQL Fiddle

    
21.06.2017 / 01:33
Working with GitHub [closed] How to leave only one border in JTextField?