SELECT with REGEXP

0

Next, I have a people table:

[name, enrollment, cpf]

What happens is, some people who have the "same" cpf have different license plates. The [same] is in quotation marks, because in the database, there are cpfs like in this example:

1) 00000000000 2) 000,000,000-00

And I need to do a query, searching for a cpf of the first example, and postgresql "understand" that the second option is also valid for me ..

How to do this?

SELECT * FROM people WHERE cpf = ???????

Thank you

    
asked by anonymous 18.11.2016 / 02:44

1 answer

1

In fact if you do not want to use regular expression you can only use replace :

SELECT *
  FROM pessoas
 WHERE replace(replace(cpf, '.', ''), '-', '') = '00000000000';

Where the first parameter is the text to be used as the base, the second is the character to be found and the third is the character that you change.

Or you can use regular expression with regex_replace where the parameters are almost the same, but the second parameter accepts regular expressions and the third one is the scope ( g to global):

SELECT *
  FROM pessoas
 WHERE regexp_replace(cpf, '[^0-9]+', '', 'g') = '00000000000';
    
18.11.2016 / 02:55