Not REGEXP_LIKE in Oracle

Question

I have a big table with phone numbers. Phone numbers are all lines and should be "[phone]" or similar. (A “+” sign followed by 9 to 13 digits.)

User error detected one line with the line '+ 987 + 9873678298'. It is clear that this should not be, and I would like to know how many other cases there are of these or those such errors.

I tried this request but it did not do the job. My thinking is something that is not like this chain. (Oh, the table is not indexed by phone number.)

SELECT user_key,
       first_name,
       last_name,
       phone_number
FROM   users u
WHERE  regexp_like(phone_number, '[^\+[0-9]*]')
AND    phone_number IS NOT NULL

Answer 1

, phone_number a '+', 9-13 , :

select *
from users 
where not regexp_like(phone_number, '^\+[0-9]{9,13}$')

:

• ^ , , 'XX [phone]'
• \+ '+'
• [0-9]{9,13} 9-13
• $ , '[phone] XX'

, , :

where not (
                /* strings of 10-14 chars */
                length(phone_number) between 10 and 14 
                /* ... whose first is a + */
            and substr(phone_number, 1, 1 ) = '+' 
                /* ...and that become a '+' after removing all the digits */
            and nvl(translate(phone_number, 'X0123456789', 'X'), '+') = '+' 
          )

, , , , , .