In SQL Server,I need to pull the records that have improper international phone number of format

Hi can you please help me to come up with a solution where i can pull records that have improper international telephone number format. like for example: These are set of records that have length 16 characters.

TelNumber.

091-3 4-43 -5678 ->here including numbers it even counts the spaces ,'-' between numbers and gives length as 16.

509--66-4 3-8887

670- 67--077-546.

908-898-654-3421 ->only 4th and 5th records are valid records that match the standard format we follow:

972-567-553-7689 ccc-aaa-nnn-nnnn where ccc =country code;aaa =area code;nnn-nnnn =phone number

I need a query that can pull first 3 record types...

thanks,

Comalkatar

I think that this will work for you:

Code Snippet

DECLARE @.Phones table
( TelNumber varchar(20) )

SET NOCOUNT ON

INSERT INTO @.Phones VALUES ( '091-3 4-43 -5678' )
INSERT INTO @.Phones VALUES ( '509--66-4 3-8887' )
INSERT INTO @.Phones VALUES ( '670- 67--077-546.' )
INSERT INTO @.Phones VALUES ( '908-898-654-3421' )
INSERT INTO @.Phones VALUES ( '972-567-553-7689' )

SELECT TelNumber
FROM @.Phones
WHERE TelNumber NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

TelNumber
--
091-3 4-43 -5678
509--66-4 3-8887
670- 67--077-546.

|||

Here You go...(using Regular Expression)

Code Snippet

Create Table #telephones (

TelNumber Varchar(100)

);

Insert Into #telephones Values('091-3 4-43 -5678');

Insert Into #telephones Values('509--66-4 3-8887');

Insert Into #telephones Values('70- 67--077-546.');

Insert Into #telephones Values('908-898-654-3421');

Insert Into #telephones Values('972-567-553-7689');

Select

*

From

#telephones

Where

TelNumber NOT Like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'