Oracle Regular Expression to match US Phone Number Format

Question

Oracle Regular Expression To match US Phone Number in the following formats only.

(NNN) NNN-NNNN or NNN-NNN-NNNN or NNNNNNNNNN

I have tried and came up to this :

with test as
(
select '[phone]' as testcol from dual
union
select '444123-6780'                from dual
union
select '6741236780'                from dual
union
select '[phone]'            from dual
union
select '447-127-6787'            from dual
union
select '447-127-3333333333'            from dual
)
SELECT *
FROM test
WHERE REGEXP_LIKE(testcol, '^\(?\d{3}\)?([[:blank:]|-])?\d{3}-?\d{4}$');

Results :

[phone]  -- valid
[phone]  -- valid
444123-6780     -- not valid
447-127-6787    -- valid
6741236780      -- valid

As you can see, I received 444123-6780 (NNNNNN-NNNN) format, which I don't require.

Is there any way to stop the above mentioned format not to match?

Answer 1

REGEXP_LIKE(testcol,'^(\(\d{3}\))([[:blank:]])\d{3}-\d{4}$|^\d{3}(-)\d{3}(-)\d{4}$|^\d{10}$')