Consult ID in database ignoring scores

Asked

Viewed 1,037 times

4

I have a mysql database where I need to make a comparison of registered Rgs with another provided by the user through an input, to see if the same ID is already registered in the database.

The problem is that as there are records with scores on RG and others without scores, there is no pattern. In this case, I thought to take the scores I receive from the input and compare with what I have in the bank, by my SQL, but for that I need to ignore the scores I already have in the RG column in my bank.

How can I do this in my SQL?
 

Note to the Portuguese: RG (General Registry) is the official identity document in Brazil. The numbering format varies from state to state, and this generates a number of problems, including the ease of the Brazilian citizen having irregularly different Rgs in different states. There was a project to unify the system by replacing the RG with the RIC, a new standardized format, but unfortunately the project was suspended.
More details on http://en.wikipedia.org/wiki/Brazilian_identity_card

mysql sql

  • By SQL? I think it is a little unnecessary, since Voce is making a program that connects to mysql, why not remove these accents by the program? what link is using, java, php? But if you really want a solution, I found in Soen http://stackoverflow.com/questions/630472/removaing-nonnumerical-data-out-of-a-number-sql (This was not tested with Mysql but should work)

  • I agree, the problem is that there are already several records before even my system runs, it is a database that already existed.

  • Thanks @bfavaretto for the explanation/edition regarding RG, including this is something I had doubts about the possible formats.

  • 1

    The ID format varies from state to state, and may or may not have a digit. In other words, you have to be careful when storing and comparing, as a short number of RG stored with digit may get confused with a larger RG of another person, but without digit. An ID taken from an SP driver’s license comes without a digit, but it should be compared equally with a digit copied from another document, for example. To make matters worse, there is a small risk of people from different states having the same number.

  • Related: http://answall.com/questions/71/valida%C3%A7%C3%A3o-de-rg

1 answer

10

You can use the function REPLACE Mysql to replace dots and dashes with nothing:

SELECT ...
FROM tabela
WHERE REPLACE(REPLACE(coluna, '.', ''), '-', '') = '123456789'

Important: according to commenting in the question, the format of Rgs is more complex and can vary by state. A number like 12345 can be either a digitless RG or a 1234-5 RG. So the safest thing would be to isolate the digit, and ideally the state, to avoid false positives and false negatives.

  • 1

    @Zuul’s ID numbers are usually something like 52.159.67-9 then yes, just . and - can be removed, but I believe that a Trim() should be applied as well, to ensure that the comparison is correct. (both in the program and in SQL. @bfavaretto doesn’t think it would be interesting to leave a Trim() in the answer?

  • 3

    @Olimonf. Trim may or may not be necessary. I would do this before saving to the bank, and before passing the input to be compared in the query.

Browser other questions tagged mysql sql

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