SQL 2008 R2 syntax

How do I change phone number records in SQL table
Most of current phone numbers in the DB written in like
XXX/XXX-XXXX format  401/396-2400
I would like to change it to
 XXX-XXX-XXXX format 401-396-2400
I am looking for an exact syntax to replace all ‘/’ with ‘-‘  for SQL 2008 R2 db
leop1212Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
John_VidmarConnect With a Mentor Commented:
replace(yourfield, '/', '-')
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Easy to replace:
--UPDATE ...
--SET phone_number =
REPLACE(phone_number, '/', '-')
--WHERE phone_number LIKE '%/%'

But better would be to remove all formatting in the value stored in the db and add it back when needed when reading from the table.
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
just get rid of all non numeric values and format when you need it...

update table set phone = replace(replace(phone,'/',''),'-','')

then, modify your app and do not allow any other character, or use 3 fields, and concat & save to db, or use some custom control that allows formatted input...

when you need it, you can always format it like:

select left(phone,3) + '-' + substring(phone, 4,3) + '-' + right(phone,4) as formatted_phone
0
All Courses

From novice to tech pro — start learning today.