Query to return specific values

0

In my table IDENTIFICACAO_PESSOA I have a column named 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 the records that are outside this pattern? And I also want records that contain more than 11 digits (numeric values).

    
asked by anonymous 02.03.2017 / 21:10

2 answers

1

Use 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}$');
    
02.03.2017 / 22:07
1
select *
from identificacao_pessoal
where length(identificador) <> 11

Tip, repack the template.

    
02.03.2017 / 23:35