3

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 '(444) 123-6780' as testcol from dual
union
select '444123-6780'                from dual
union
select '6741236780'                from dual
union
select '(445) 123-6781'            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 : 

(444) 123-6780  -- valid
(445) 123-6781  -- 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?

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
user2018441
  • 63
  • 1
  • 1
  • 8
  • 2
    Interestingly, none of your numbers are valid North American phone numbers because the first and fourth digits cannot be 1's. http://en.wikipedia.org/wiki/North_American_Numbering_Plan – David Aldridge Jan 28 '13 at 15:18

2 Answers2

1
REGEXP_LIKE(testcol,'^(\(\d{3}\))([[:blank:]])\d{3}-\d{4}$|^\d{3}(-)\d{3}(-)\d{4}$|^\d{10}$')
user2018441
  • 63
  • 1
  • 1
  • 8
  • 1
    I thought it could be done with a single regex string ;)... But because of `([[:blank:]|-])?` the earlier regex will match a string having format `NNNNNN-NNNN [444123-6780]` which is not required. So , I made regex string for each format and, `OR` them together – user2018441 Dec 16 '14 at 12:11
0

try this reg ex.. it will help you

REGEXP_LIKE(testcol,'^\([0-9]{3}\)[:space:]?[0-9]{3}(-|[:space:])?[0-9]{4}$|^[0-9]{3}-?[0-9]{3}-?[0-9]{4}$')

Source From : http://psoug.org/snippet/Regular-Expressions--RegExp-examples-3_859.htm

Pandian
  • 8,848
  • 2
  • 23
  • 33