How to format a numeric column as phone number in SQL

Question

I have table in the database with a phone number column. The numbers look like this:

123456789 

I want to format that to look like this:

123-456-789 

Answer 1

This should do it:

UPDATE TheTable SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + '-' +                    SUBSTRING(PhoneNumber, 4, 3) + '-' +                    SUBSTRING(PhoneNumber, 7, 4) 

Incorporated Kane's suggestion, you can compute the phone number's formatting at runtime. One possible approach would be to use scalar functions for this purpose (works in SQL Server):

CREATE FUNCTION FormatPhoneNumber(@phoneNumber VARCHAR(10)) RETURNS VARCHAR(12) BEGIN     RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' +             SUBSTRING(@phoneNumber, 4, 3) + '-' +             SUBSTRING(@phoneNumber, 7, 4) END