format phone field

Hello,

I have a phone number field with the format (123)-456-7890 I need to convert this to 1234567890 formats while I am retrieving data from the table. How can I do this?

My first thought is to use SUBSTRING(...) in your SELECT query. For example...

SELECT (SUBSTRING(PHONE,2,3) + SUBSTRING(PHONE,7,3) + SUBSTRING(PHONE,11,4)) AS PHONE FROM YOURTABLE

This assumes that all your phone records are in the format you described.

|||

Thanks for the reply, will this fail if there is nothing in Phone, if yes, hat can I do to solev that problem?

|||

SELECT REPLACE(REPLACE(REPLACE(Phone,'(',''),')',''),'-','') AS Phone

Basically that just removes all ()- from the phone field before returning it to you.

|||Do whatmotleysuggested. I'm not sure, but youmayneed to use ISNULL(PHONE,'') in place of PHONE if your PHONE column is nullable.

SELECT REPLACE(REPLACE(REPLACE(ISNULL(Phone,''),'(',''),')',''),'-','') AS Phone