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