Spliting a Full Name data into First Name, Last Name columns

Quite often when working with a old database where a single column been used to store both first name and last name. Now we have to separate them into two columns.

The following code is an example of how to split a full name columns into first name and last name columns. 


SELECT

LEFT (EMPLOYEE_FULL_NAME, CHARINDEX(' ',EMPLOYEE_FULL_NAME))as 'Employee First Name' 

,SUBSTRING (EMPLOYEE_FULL_NAME, CHARINDEX(' ',EMPLOYEE_FULL_NAME)+1, LEN(EMPLOYEE_FULL_NAME)-(CHARINDEX(' ',EMPLOYEE_FULL_NAME)-1 ))

 as 'Employee Last Name'

 

FROM [dbo].[HR_Employee_table]