Best way to validate telephone number

Question

Best way to validate telephone number

rash3554

SSCommitted

Points: 1984
More actions
November 3, 2014 at 8:54 am

#298022

Hi there,
I have a table of 4 million records that contain Telephone # field. I want to count the number of digits and if it is 10 or 11 then I want to display it. Valid phone numbers are (111) 555-12-34 OR (111) 555-1234 OR 1-111-555-1234,(11115) 55-512 OR 1-111-555-1234.
I wrote this select statement to get those records that are length 10 or 11
CREATE TABLE [dbo].[temp](
Telephone varchar(20)
) ON [PRIMARY]
Insert into temp values('(111) 555-12-34');
Insert into temp values('(111) 555-1234');
Insert into temp values('1-111-555-1234');
Insert into temp values('(11115) 55-512');
Insert into temp values('11-555-1234');
select telephone from temp where
((len(Replace(Replace(Replace(Replace(telephone,'(',''),')',''),'-',''),' ','')) = 10) OR (len(Replace(Replace(Replace(Replace(telephone,'(',''),')',''),'-',''),' ','')) = 11))
Is there a better way of processing this?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Take a look at these validation functions: http://www.sqlservercentral.com/blogs/pearlknows/2011/04/05/sql-validation-functions-cool-udf-s-to-have-handy/

-- Gianluca Sartori

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You're missing a validation to get only numeric values. A value like 'ABCDE67890' would still pass your test. I'm leaving 2 options that validate for digit-only values.

Information of this function on the following article

http://www.sqlservercentral.com/articles/String+Manipulation/94365/

SELECT Telephone

FROM temp

CROSS APPLY dbo.PatternSplitCM( Telephone, '%[0-9]%')

WHERE Matched = 1

GROUP BY Telephone HAVING SUM(LEN(Item)) IN( 10, 11)

Information on this function on this thread

http://www.sqlservercentral.com/Forums/FindPost1629767.aspx

SELECT *

FROM temp

CROSS APPLY dbo.DigitsOnlyEE( Telephone)

WHERE LEN( DigitsOnly) IN (10, 11)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101122 More actions · Answer 3

Best is to always strip non-numeric chars before storing the value. If you have to, store both the original, edited version and a stripped version, or, better yet, a tinyint code that identifies the edited format. Then it's a very simple matter of:

LEN(column) BETWEEN 10 AND 11

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

What is the [font="Arial Black"]BEST [/font]way? The answer is to follow the telephone number rules. For example, there is no area code that starts with 0 or 1. Then validate against the NANPA valid area code and exchange list which you can get from the North American Numbering Plan Association at http://www.nanpa.com/reports/reports_cocodes_assign.html and, yes... it's free. Beat that up against other specific lists on that web site that identify "special" and "reserved" NPA/NXX combinations along with special full numbers.

A company called CCMI provides all information for the U.S. and the rest of the world but that's not free. Telcordia provides similar information but they're not free either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 5

Chipping in my 2 Cents, it is close to impossible to validate a phone number without a geographical reference, with it, it can still cause an headache.

On the OP, this thread might be helpful

😎