how to format phone number in sql server

My database has phone numbers, some that are already formatted correctly and some that are null.

I need a command that I can run on the server to updated these numbers so they are all in this format  xxx-xxx-xxxx.

Not sure how to weed out the ones already in that format and the nulls. And also verify there are 10 digits before applying format.

I'm using sql server 2005

Thanks.
rutledgjAsked:
Who is Participating?
 
wdosanjosCommented:
Please try:
update TheTable
set phone = substring(phone,1,3) + '-' + substring(phone,4,3) + '-' + substring(phone,8,4)
where phone not like '___-___-____' and len(phone) = 10

Open in new window

0
 
wdosanjosCommented:
Are the values to be formatted all numbers already?  Or they come in a variety of different formats (for example, (xxx) xxx-xxxx)?
0
 
rutledgjAuthor Commented:
All are numbers but some already have the dashes there. These don't need to be changed.
0
 
rutledgjAuthor Commented:
Some entries are null
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.