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

ID PhoneNumber
1 +1 3039293143
2 +1 3059293143

Expected Output

Id USPhoneNumber
1 303-929-3143
2 305-929-3143

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”.

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