[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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.
0
rutledgj
Asked:
rutledgj
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now