Storing US Phone Numbers

Letting users enter their phone number as plain text and just storing that text in your database as it was input can lead to future headaches when it comes to working with those phone numbers. The following are actual formatting examples that were in a client’s database. The numbers have obviously been changed to provide anonymity, but you can substitute the ‘5’ with any number.

555-555-5555, 5555555555, 555*555-5555, 555.555.5555, 555 555 5555, 555 555-5555, (555)5555555, (555) 555-5555, None, 555-5555, 555-5555555-, NA, 555, 5555555

As you can see, by letting the users control how the phone number is entered and storing it as such, results in many variations. Developers have various opinions about storing phone numbers in databases. Below are some representations of novice developer’s thoughts on storing phone numbers.

The User Should Be In Charge Of What Format The Data Is In, Right?

WRONG! This idea comes from the thought that the user is the one entering the data, so if they want to enter and later be shown parenthesis in their number, then they should have that option. If the user prefers dashes or periods, that’s okay also.

PROS: The user has full control of the data that is stored. It can be shown back to the user the same way it was entered. It can account for extensions and if the users phone number spells a word, they can enter the word in instead of the numbers. (rare, but I suppose some businesses may do this)

CONS: The user has full control of the data. Developers will have to tweek the data how it should display for every data exit point that uses your database. Your sales staff would probably like the data shown in a standard US format, instead of each users phone number showing in a different format. To accomplish this, your developer would need to someone change each user’s phone number format when it is queried out of the database. The more apps you connect to your database, this work will have to be duplicated.

How The Data Is Stored Is Irrelevant, At Least We Have It

WRONG! If your primary objective is to get data, no matter if it is correct, if the individual took the time to input actual data, or if the data is even valid at all, winds up costing you money down the sales pipeline. There must be some filtering of data in place. Having wrong or ‘crap’ data should never be acceptable. If you allow a user to input all fives, like this 5555555555, that’s obviously bogus data. If you allow a user to input ‘N/A’ or ‘None’, on a required phone number field, there is something wrong with your app. If you simply don’t care about what they enter, then it should NOT be a mandatory field; but if they do enter something in a phone number field, make sure the data is of a valid format. Not having a valid format means that auto-dialing software, API text messaging apps and other phone number apps you may want to integrate in the future will have a difficult time accessing your data.

Validating The Phone Number Format Is Too Much Work

Lazy Developer! For those that actually think this, why are you a developer to begin with? You should be solving problems, not creating new ones by writing code that doesn’t take into account user error. Users will enter whatever is available to them at some point in time. You should be striving to create software that works, and that can’t be tricked or hacked to do other unintended things. Most times that involves filtering and validating user input. If you think that it’s too much work, I wonder if you’re bothering to tighten your security around SQL injection, CSRF attacks, XSS, brute force attacks and other security concerns plaguing the internet these days.

Our Solution

We’re only taking into account United States phone numbers here because that’s primarily who we work with. The two most common phone number formats in the United States are 1-555-555-5555 & (555) 555-5555. The first one, with only digits and dashes, is what we’ll work with. First off, we let the user input the number however they choose, be it dashes, parenthesis, etc. We then strip all non-numeric characters and save the result into our database. We can optionally limit the non-numeric value to less than or equal to 10 characters, which should cover all the US phone numbers. If it’s 11 digits and the first digit is a one, we drop that digit.

Phone numbers in our database are stored as decimal(10). Example: 5555555555

Formatting the phone number when displaying it to  clients or sales staff, we simply add dashes like so:
print substr($phonenum, 0, 3).”-“.substr($phonenum, 3, 3).”-“.substr($phonenum,6);

This method of storing the phone number keeps extraneous characters like *, (), dashes, ‘NA’ & ‘None’ out of our database and allows the script to determine how to display it. A database is for storing information and being able to search for information quickly. This input method provides the quickest sorting, ordering and search-ability.

Various Notes:

Of the 540 users that filled out their optional phone number in one of our client’s web apps,  2.04% of them input a value that didn’t have 10 numbers in it. The ill-formatted phone numbers included a few 7-digit numbers (probably missing the area code), a few 11-digit phone numbers (fat-fingered a number perhaps), a few ‘None’ values, and a 3-digit number (lazy/careless user).