Phone Number Formatting

[Tech2377]

Phone numbers are already stored in the following formats:

000-000-0000
(000)000-0000
0000000000
(000)-000-0000

On the query, I want the phone numbers show in (000)-000-0000 format regardless the phone numbers stored in the db.

select phonenumber ...
from demo
...

 

[mrdenny]

Formatting should be done on the client application or report not by the SQL Server. Front end applications typically have much better tools for changing the formatting of text strings.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[fredericofonseca]

As mrdenny said, client application should do it. Not only because they have better tools to do it, but also because doing it on the server will degrade performance.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SQLSister]

I would suggest changing your database so that only the numbers in the phone field are stored. Then it is trivial to show in whatever format you want. We get phone data from many many sources and this is the best way we have found to handle the problem. Data is entered consistently and if one cliuent wants it to display (###)###-#### and one wants it to display ###-###-####, it isn't a problem. Plus scrubbing for numbers only weeds out junk like "Call Susan instead of him" We also never store a phone number that doesn't have the correct number of digits including the area code. If we can't call the number, it is useless to store. We store extensions in a separate field.

To accomplish this you will need to set up your field so that it will to accept numbers and set up your user interface to check the data before it is entered or updated and to only send numbers and change any imports to scrub the numbers before inserting them. These are one-time changes which will apear to be time-consuming but which will save much processing time for the future and as therefore worth the time to do. When there is a clear standard for waht the data should contain, the controls to ensure so other kind of data is put into the field should always be both on the database itself and in the user-inteerface. The saying garbage in, garbage out is very true. If you have to spend a lot of time fixing teh data when you want to display it, then you need to fix how the data is entered and stored instead. Every single time. No matter how painful it is.

"NOTHING is more important in a database than integrity." ESquared