Feeds:
Posts
Comments

Posts Tagged ‘How to format phone numbers’

Formatting phone number has never been an issue, the only thing that you need to do is, use few string functions and you can convert it into any format. But what encourages me to write this article is that we can format the phone numbers in any format using only one function, i.e. Format (introduced in SQL Server 2012).

Note : I usually recommend that phone number must be properly maintained inside the database. But sometimes we come across this issue in legacy systems.

Sample :
Let me create a sample to explain it.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Phone Numbers] INT
)
GO
INSERT INTO tbl_sample VALUES (1,3333333333)
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

ID Phone Numbers
———– ————-
1 3333333333

(1 row(s) affected)

SOLUTION :
In the solution, we will use FORMAT function and provide a custom format of telephone number such that,  the Format function will automatically convert it into custom format.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
 [Phone Numbers]
,FORMAT([Phone Numbers],'###-###-####') AS [Formatted Phone]
FROM tbl_sample
UNION ALL
SELECT
 [Phone Numbers]
,FORMAT([Phone Numbers],'(###) ###-####') AS [Formatted Phone]
FROM tbl_sample
GO
--OUTPUT

Format_phone_number.1.1

Read Full Post »