TSQL: Split Full Name into First Name and Last Name Columns

If you need to create an ad-hoc report and split full name into first name and last name, online search will suggest to create a custom user defined function.

Sometimes, it is not possible to create an user defined function for single ad-hoc report because you need to follow change control process of your organization and you have limited time.

If you are looking for a SQL Server built in function, to split full name into separate columns, use PARSENAME. Please note that this function is used to parse SQL object names which are separated by “.” (dot) character.

Limitations of PARSENAME

If you are fine with the following limitations, you may use this built-in function for your need.

• It only splits string with “.” (dot/full stop) as a separator so you may need to replace your separator character(s) with “.” (dot/full stop).
• It only splits maximum of 4 parts.
• All rows must have data in the exact parts.
• You cannot have one row with 4 parts and another row with 2 parts.
• Result (not input) must be of nvarchar(128) or varchar(256). If you have longer result, it will return null value.

TSQL Example – Split Full Name Into First Name and Last Name Columns

PARSENAME function 2nd argument is in reverse order – i.e. from right to left. So make sure you are using the correct argument based on your need.

DECLARE @EmployeeName VARCHAR(120)
DECLARE @EmployeeName2 VARCHAR(120)

SELECT @EmployeeName = 'Vishal Monpara'
SELECT @EmployeeName2 = 'Monpara, Vishal'

SELECT PARSENAME(REPLACE(@EmployeeName, ' ', '.'), 2) AS FirstName, PARSENAME(REPLACE(@EmployeeName, ' ', '.'), 1) AS LastName

SELECT PARSENAME(REPLACE(@EmployeeName2, ', ', '.'), 1) AS FirstName, PARSENAME(REPLACE(@EmployeeName2, ', ', '.'), 2) AS LastName