SQL Puzzle | The Phone Number Puzzle

In this puzzle we have to manipulate the column PhoneNumber to get the phone numbers in the US format. The expectation is minimal transformation.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--


CREATE TABLE USNumber
(
	 ID INT 
	,PhoneNumber VARCHAR(13)
)
GO

INSERT INTO USNumber VALUES (1, '+1 3039293143'),(2,'+1 3059293143') 
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1 – SQL 2012 +

--
 
SELECT Id, FORMAT(CAST(REPLACE(PhoneNumber,'+1 ','') AS BIGINT),'###-###-####') USPhoneNumber
FROM USNumber

--

Solution 2 – Below SQL 2012

--
 
SELECT Id, CONCAT(LEFT(USPhoneNumber,3),'-',SUBSTRING(USPhoneNumber,4,3),'-',RIGHT(USPhoneNumber,4)) USPhoneNumber
FROM
(
	SELECT Id, REPLACE(PhoneNumber,'+1 ','') USPhoneNumber
	FROM USNumber
)r

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com