I HAVE THIS FREE FLOWING DATA FIELD IN A VARCHAR2 COLUMN THAT I AM CLEANING UP.


I WOULD LIKE TO GET THOSE VALUES THAT HAVE A TOTAL OF 10 DIGITS IN ALL AND MIGHT CONTAIN THESE CHARACTERS : . OR - OR / OR ( )

CREATE TABLE E ( PHONE VARCHAR2(50));


insert into e values('Tom phone number is 1234567890');
insert into e values('Tom phone number is 123.456.7890');
insert into e values('Tom phone number is 123-456-7890');
insert into e values('Tom phone number is 123/456/7890');
insert into e values('Tom phone number is (123)456-7890');
insert into e values('Tom phone number is');
insert into e values('Tom phone number is 11');
insert into e values('Tom Phone 1234567 is number is 123456789');
insert into e values('Tom Phone 123.4567 is number is 123.456.789');
insert into e values('Tom Phone is number is 123.456.789');

I AM LOOKING FOR THE RESULT THAT ONLY CONTAINS 10 DIGITS. SO ANYTHING THAT HAS 10 CONSEQUTIVE NUMBERS OR 10 NUMBERS WITH A - OR A . OR A ( ) IN IT.

THE RESULT TO LOOK LIKE:
1234567890
1234567890
1234567890
1234567890
1234567890

SO RECORDS FROM THE 1ST 5 ROWS AS OTHER RECORDS DO NOT SATISFY MY REQUIREMENT OF 10 DIGITS.

THANKS A LOT
SHARVU

select phone,
       translate ( phone, '1.-/()', '1' ) removed_chars, 
       regexp_substr ( 
         translate ( phone, '1.-/()', '1' ), 
         '[[:digit:]]{10}' 
       ) numbers 
from   e
where  regexp_like ( 
         translate ( phone, '1.-/()', '1' ), 
         '[[:digit:]]{10}' 
       );

PHONE                               REMOVED_CHARS                    NUMBERS      
Tom phone number is 1234567890      Tom phone number is 1234567890   1234567890   
Tom phone number is 123.456.7890    Tom phone number is 1234567890   1234567890   
Tom phone number is 123-456-7890    Tom phone number is 1234567890   1234567890   
Tom phone number is 123/456/7890    Tom phone number is 1234567890   1234567890   
Tom phone number is (123)456-7890   Tom phone number is 1234567890   1234567890