What datatype should be used for storing phone numbers in SQL Server 2005

Question

I need to save contact information in a table. Which datatype should I use, please advise. Wait. Please continue reading before responding.

Given that sales representatives can utilize this field for searches, it needs to be heavily indexed (including wild character search).

We currently anticipate that phone numbers will come in a variety of formats (from an XML file). To convert to a standard format, do I need to develop a parser? It's possible that there may be millions of data (many of which will be duplicates), and I don't want to use up too many server resources on preprocessing each time any new source data is received.\

Can someone please help me with this?

Answer 1

Does this include:

• International numbers?
• Extensions?
• Other information besides the actual number (like "ask for bobby")?

If all of these are no, I would use a 10 char field and strip out all non-numeric data. If the first is a yes and the other two are no, I'd use two varchar(50) fields, one for the original input and one with all non-numeric data striped and used for indexing. If 2 or 3 are yes, I think I'd do two fields and some kind of crazy parser to determine what is an extension or other data and deal with it appropriately. Of course, you could avoid the 2nd column by doing something with the index where it strips out the extra characters when creating the index, but I'd just make a second column and probably do the stripping of characters with a trigger.

I hope this helps you.