Locate record in Mysql by specific character

Asked

Viewed 1,344 times

2

I have a bank called completo, whose table dados is:

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

I would like to 'Filter' all Cpfs q contain a certain 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 by ignoring the two check digits / verifiers. An example of how to determine this number is CPF no XXX.XX.X6-XX. Highlighted we see the last valid number of the CPF, ignoring the check digits."

I want to search in the bank all Cpfs from Rio de Janeiro, for example, according to this relationship:

 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.
mysql

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 number of the desired state:

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.

  • Grateful Friend Helped Me

  • @Leocaracciolo which error is returning ?

  • @Leocaracciolo that bizarre, Oo now I’m confused, will the guy positively me wrong?

  • And @Leocaracciolo his answer is incorrect, he wants the 9°; digito being the number of the state, his is pulling the last one, the 11°. I’ll open it in the bank here.

  • @Leocaracciolo Yours is not returning anything because there is no digit '8' in the ninth position in any of the records in your table :)

  • @Leocaracciolo Because it has number '8' in the 11° position, but the correct is to have in the ninth, yours is right, just by "SELECT * FROM data WHERE SUBSTRING(Cpf,9) = '8'"

  • It is , ai depends on the digit you put, if you put "SELECT * FROM WHERE SUBSTRING data(Cpf,9) = '5'" in your database will return results pq there is '5' in the ninth position.

  • @Leocaracciolo You’re right, I’ll edit my answer.

  • I was confusing it with the Javascript substring too... but thanks for the fix.

  • 1

    The answer is now correct, it is always suggested to complement with a SQL Fiddle to value the response with a demonstration.

Show 5 more comments

1

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

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

List with 8 example Cpfs

Lista com 8 CPFs de exemplo

Query result, showing only Cpfs whose 9th position is equal to 7:

In SQL Fiddle

On my server:

Resultado da query, mostrando apenas CPFs cuja nona posição é 7

Another option is the function replace SELECT * FROM dados where substr(cpf,-3,1) = 7; SQL Fiddle

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.