Sql - format phone number

Question

HI ,

i have table in the db with phone number column the numbers look like this

123456789

i want to format tham to 123-456-789

thanks!!!

Answer 1

This should do it:

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

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(10)
BEGIN
    RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7)
END

Answer 2

Hi,

I'd generally recommend you leave the formatting up to your front-end code and just return the data as-is from SQL. However, to do it in SQL, I'd recommend you create a user-defined function to format it. Something like this:

CREATE FUNCTION [dbo].[fnFormatPhoneNumber](@PhoneNo VARCHAR(20))
RETURNS VARCHAR(25)
AS
BEGIN
DECLARE @Formatted VARCHAR(25)

IF (LEN(@PhoneNo) <> 9)
    SET @Formatted = @PhoneNo
ELSE
    SET @Formatted = LEFT(@PhoneNo, 3) + '-' + SUBSTRING(@PhoneNo, 4, 3) + '-' + SUBSTRING(@PhoneNo, 7, 3)

RETURN @Formatted
END
GO

Which you can then use like this:

SELECT [dbo].[fnFormatPhoneNumber](PhoneNumber) AS PhoneNumber
FROM SomeTable

It has a safeguard in, in case the phone number stored isn't the expected number of digits long, is blank, null etc - it won't error.

EDIT: Just clocked on you want to update your existing data. The main bit that's relevant from my answer then is that you need to protect against "dodgy"/incomplete data (i.e. what if some existing values are only 5 characters long)