Not REGEXP_LIKE in Oracle
If you need to find all the rows where phone_number
is not made by exactly a '+'
followed by 9-13 digits, this should do the work:
select *from users where not regexp_like(phone_number, '^\+[0-9]{9,13}$')
What it does:
^
the beginning of the string, to avoid things like'XX +123456789'
\+
the '+'[0-9]{9,13}
a sequence of 9-13 digits$
the end of the string, to avoid strings like'+123456789 XX'
Another way, with no regexp, could be the following:
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'), '+') = '+' )
This could be faster than the regexp approach, even if it's based on more conditions, but I believe only a test will tell you which one is the best performing.