Query to return specific values

Asked

Viewed 158 times

0

On my table IDENTIFICACAO_PESSOA I have a column called IDENTIFICADOR. This column receives CPF values. However, there are values there that do not correspond to CPF, that is, they are outside the standards of a CPF (999.999.999-99). How would a query return records that are outside this standard ? And I also want records that contain more and less than 11 digits (numerical values).

sql oracle

  • You could use regular expression in your SQL query. http://www.devmedia.com.br/expressoes-regulares-em-banco-de-dados-muito-alem-do-like-parte-02/12790

  • You will have to create a function, to validate whether Cpf is valid or not and then use this function in your query

2 answers

1


Utilize REGEXP_LIKE, as follows:

SELECT *
FROM IDENTIFICACAO_PESSOA
WHERE NOT REGEXP_LIKE(IDENTIFICADOR, '^[0-9]{3}\.[0-9]{3}\.[0-9]{3}\-[0-9]{2}$');

  • Returned me what’s in the pattern.

  • Returns that is not in the pattern: notice the "NOT".

  • Strange, while running here returned what is in the pattern

  • I adjusted the answer, try again! It will pick up if it’s not exactly the default now.

  • Has the answer resolved what was in doubt? Do you think it is possible to accept it now?

1

select *
from identificacao_pessoal
where length(identificador) <> 11

Tip , rethink the model.

  • In your case it returns me anything other than 11, and the query to know who is outside the pattern ?

  • Since 11 is the default CPF (assuming left zeros are written) yes.

Browser other questions tagged sql oracle

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