Split One Column like Full Name to First Name, Middle Name, Last Name
- Using CharIndex, Reverse, RTrim, Substring in TSQL
SELECT [Child Name],LEFT([Child Name],CHARINDEX(' ',[Child Name] + ' ')-1) AS First_Name,
Rtrim(substring([Child Name],CHARINDEX(' ',[Child Name],0)+1,len([Child Name])-CHARINDEX(' ',[Child Name],0)+1-CHARINDEX(' ', REVERSE([Child Name])))) Middle_Name,
Right([Child Name],ISNULL(NULLIF(CHARINDEX(' ', REVERSE([Child Name])) - 1,-1), LEN([Child Name]))) AS Last_Name
From Child
