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
Example for formate :- (804) 901-7152
select ‘(‘+(SUBSTRING(Phone,1,3)+’) ‘+SUBSTRING(Phone,4,3) +’-‘ +SUBSTRING(Phone,7,4)) as Phone from Customer
OR
select ‘(‘+(Left(Phone,3)+’) ‘ + SUBSTRING(Phone,4,3)+’-‘+ RIGHT(Phone,4)) as Phone from Customer