SQL Puzzle | The String Puzzle [Split Puzzle]

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

3 thoughts on “SQL Puzzle | The String Puzzle [Split Puzzle]”

oswalmitesh said:

November 25, 2016 at 10:00 PM

Hi,

DECLARE @RemoveDuplicateWords TABLE
(
Id INT
,InputString VARCHAR(1000)
)

DECLARE @tblNumber TABLE
(
Id INT
)

INSERT INTO @tblNumber
SELECT TOP 1000 ROW_NUMBER() OVER( ORDER BY so.Object_id)
FROM sys.objects so,sys.objects si,sys.objects st

DECLARE @EmployeesTest TABLE
(
[EmployeeID] [int] NOT NULL,
[FullName] [varchar](50) NULL
)

INSERT @EmployeesTest ([EmployeeID], [FullName]) VALUES (1, N’Nancy Davolio’)
INSERT @EmployeesTest ([EmployeeID], [FullName]) VALUES (2, N’Andrew W Fuller Jr’)
INSERT @EmployeesTest([EmployeeID], [FullName]) VALUES (3, N’Janet Leverling’)
INSERT @EmployeesTest ([EmployeeID], [FullName]) VALUES (4, N’Margaret Peacock’)
INSERT @EmployeesTest ([EmployeeID], [FullName]) VALUES (5, N’Steven Buchanan’)
INSERT @EmployeesTest ([EmployeeID], [FullName]) VALUES (6, N’Michael J Suyama Jr’)
INSERT @EmployeesTest ([EmployeeID], [FullName]) VALUES (7, N’Robert T King III’)
INSERT @EmployeesTest ([EmployeeID], [FullName]) VALUES (8, N’Laura Callahan’)
INSERT @EmployeesTest([EmployeeID], [FullName]) VALUES (9, N’Anne Dodsworth’)
INSERT @EmployeesTest([EmployeeID], [FullName]) VALUES (10, N’Mitesh’)
INSERT @EmployeesTest([EmployeeID], [FullName]) VALUES (11, N’Mitesh Sohan Oswal’)

SELECT EmployeeID,fullname,
MAX(CASE WHEN RN = 1 THEN E.String END) FirstName,
MAX(CASE WHEN (CRN>2 AND RN = 2) THEN E.String END) Initial,
MAX(CASE WHEN (CRN IN(2,3) AND RN= CRN) OR(CRN=4 AND RN=3) THEN E.String END) LastName,
MAX(CASE WHEN RN = 4 THEN E.String END) Suffix
FROM
(
SELECT E.EmployeeID,E.fullname,SUBSTRING(E.fullName+’ ‘,id,CHARINDEX(‘ ‘,E.fullName+’ ‘,ID)-ID)String
,ROW_NUMBER() OVER(PARTITION BY E.EmployeeID ORDER BY t.ID) RN
,COUNT(1) OVER(PARTITION BY E.EmployeeID ) CRn
FROM @EmployeesTest E
INNER JOIN @tblNumber t
ON LEN(E.fullName)>=t.id
WHERE SUBSTRING(‘ ‘+E.fullName,id,1) = ‘ ‘
–AND E.[EmployeeID] = 2
)E
GROUP BY EmployeeID,fullname

Regards,
Mitesh Oswal

LikeLike

Pawan Kumar Khowal said:

November 26, 2016 at 6:09 AM

Excellent bro !

LikeLike

revank219 said:

July 28, 2021 at 1:46 PM

select *,
substring(FullName,1,CHARINDEX(‘ ‘,FullName))firstname,
case when len(FullName)-len(Replace(FullName,’ ‘,”)) >1 then substring(FullName,CHARINDEX(‘ ‘,fullname),2) else ‘ ‘ end as initial,
case when len(FullName)-len(Replace(FullName,’ ‘,”)) >1 then substring(FullName,charindex(‘ ‘,fullname,charindex(‘ ‘,fullname)+2),CHARINDEX(‘ ‘,FullName)) else
substring(FullName,CHARINDEX(‘ ‘,FullName),len(FullName)) end as Lastname,
case when len(FullName)-len(Replace(FullName,’ ‘,”)) >1 then substring(fullname,charindex(‘ ‘,fullname,charindex(‘ ‘,fullname)+3),len(fullname)) else ‘ ‘ end as Suffix
from [EmployeesTest]

LikeLiked by 1 person

Improving my SQL BI Skills

3 thoughts on “SQL Puzzle | The String Puzzle [Split Puzzle]”

Leave a comment Cancel reply

Share this

Related

3 thoughts on “SQL Puzzle | The String Puzzle [Split Puzzle]”

Leave a comment Cancel reply