Tags

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


SQL Puzzle | The String Puzzle [Split Puzzle]

Write a T-SQL to fetch first Name, Initial, Last Name and Suffix from Full Name Column.
Please check out the sample input and the expected output.

Sample Inputs

EmployeeID FullName
1 Nancy Davolio
2 Andrew W Fuller Jr
3 Janet Leverling
4 Margaret Peacock
5 Steven Buchanan
6 Michael J Suyama Jr
7 Robert T King III
8 Laura Callahan
9 Anne Dodsworth

Expected Output

EmployeeID FullName FirstName Initial LastName Suffix
1 Nancy Davolio Nancy   Davolio  
2 Andrew W Fuller Jr Andrew W Fuller Jr
3 Janet Leverling Janet   Leverling  
4 Margaret Peacock Margaret   Peacock  
5 Steven Buchanan Steven   Buchanan  
6 Michael J Suyama Jr Michael J Suyama r
7 Robert T King III Robert T King III
8 Laura Callahan Laura   Callahan  
9 Anne Dodsworth Anne   Dodsworth

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE [dbo].[EmployeesTest](
	[EmployeeID] [int] NOT NULL,
	[FullName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (1, N'Nancy Davolio')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (2, N'Andrew W Fuller Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (3, N'Janet Leverling')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (4, N'Margaret Peacock')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (5, N'Steven Buchanan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (6, N'Michael J Suyama Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (7, N'Robert T King III')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (8, N'Laura Callahan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (9, N'Anne Dodsworth')
GO

--

SOLUTION – 1


--

SELECT EmployeeID,FullName, FirstName,Initial , REPLACE(LName,CASE WHEN rt > 0 
THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END,'') LastName, 
CASE WHEN rt > 0 THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END Suffix FROM 
(
	SELECT *, RTRIM(LTRIM(REPLACE(LastName,Initial,''))) LName , 
                  CHARINDEX(N' ', RTRIM(LTRIM(REPLACE(LastName,Initial,''))),0) rt
	FROM 
	(
		SELECT *, SUBSTRING(RTRIM(LTRIM(LastName)) , 0, CHARINDEX(N' ',RTRIM(LTRIM(LastName)),1) ) Initial 
		FROM 
		(
			SELECT *, SUBSTRING(FullName , 0, CHARINDEX(N' ',FullName,1) ) FirstName				
					, SUBSTRING(FullName , CHARINDEX(N' ',FullName,1) , DATALENGTH(FullName) ) LastName
			FROM [EmployeesTest]
		)l
	)n
)b

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Advertisements