SELECT with REGEXP

Asked

Viewed 919 times

0

next, I have a table of people:

[name, license plate, Cpf]

What happens is, some people who have the "same" Cpf have different enrollments. [Same] is in quotes, 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

sql postgresql query

  • It has a way without regex tbm, it has to be with regex?

1 answer

1


Actually if you don’t want to use regular expression you can only use one replace:

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

Where the first parameter is the text to be used as the basis, the second is the character to be found and the third is the character to be exchanged.

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

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

Browser other questions tagged sql postgresql query

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