Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Phone formatting puzzle

In this puzzle you have a column called EmployeePhone.
if a EmployeePhone is 7 digits the column should be formatted like 111-4567
if a EmployeePhone is 10 digits the column should be formatted like (123)111-3456

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

Sample Input

EmployeeID LastName FirstName EmployeePhone
1 Duck Donald 1234567
2 Duck Daffy 2088675902
3 Bunny Bugs 3158675309
4 Leghorn Foghorn 8526475
5 Martian Marvin 4159864255
6 Lepew Pepe 4537896

Expected Output

EmployeePhone (No column name)
1234567 123-4567
2088675902 (208)867-5902
3158675309 (315)867-5309
8526475 852-6475
4159864255 (415)986-4255
4537896 453-7896

Script

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

--

CREATE TABLE [dbo].Emps
(
	[EmployeeID] [int] NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[EmployeePhone] [varchar](20) NULL
)
GO


INSERT [dbo].Emps ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (1, N'Duck', N'Donald', N'1234567')
INSERT [dbo].Emps ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (2, N'Duck', N'Daffy', N'2088675902')
INSERT [dbo].Emps ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (3, N'Bunny', N'Bugs', N'3158675309')
INSERT [dbo].Emps ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (4, N'Leghorn', N'Foghorn', N'8526475')
INSERT [dbo].Emps ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (5, N'Martian', N'Marvin', N'4159864255')
INSERT [dbo].Emps ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (6, N'Lepew', N'Pepe', N'4537896')

--

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


--

SELECT EmployeePhone,   
    CASE WHEN LEN([EmployeePhone]) = 7  THEN LEFT([EmployeePhone],3) + '-' + SUBSTRING([EmployeePhone],4,LEN([EmployeePhone]))
		 WHEN LEN([EmployeePhone]) = 10 THEN CONCAT('(',LEFT([EmployeePhone],3),')') +SUBSTRING([EmployeePhone],4,3)+'-'+SUBSTRING([EmployeePhone],6,LEN([EmployeePhone]))
		 END [FormattedEmployeePhone]
FROM dbo.Emps

--

Solution – 2


--

SELECT EmployeePhone,   
    CASE WHEN LEN([EmployeePhone]) > 7 THEN '(' + LEFT([EmployeePhone], LEN([EmployeePhone]) - 7) + ')' ELSE '' END 
	+ STUFF(RIGHT([EmployeePhone], 7), 4, 0, '-')
FROM dbo.Emps

--

Output


/*------------------------
SELECT EmployeePhone,   
    CASE WHEN LEN([EmployeePhone]) > 7 THEN '(' + LEFT([EmployeePhone], LEN([EmployeePhone]) - 7) + ')' ELSE '' END 
	+ STUFF(RIGHT([EmployeePhone], 7), 4, 0, '-')
FROM dbo.Emps
------------------------*/
EmployeePhone        
-------------------- ------------------------------
1234567              123-4567
2088675902           (208)867-5902
3158675309           (315)867-5309
8526475              852-6475
4159864255           (415)986-4255
4537896              453-7896

(6 row(s) affected)

--

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