Sql – Splitting a Full Name into First and Last Name

sqlsql servertsql

I have a list of customer whose name is given as a full name.
I want to create a function that takes the full name as parameter and returns the first and last name separately. If this is not possible I can have two separate functions one that returns the first name and the other that returns the last name. The full name list contains names that have a maximum of three words.
What I want is this:-

  • When a full name is composed of two words. The first one should be
    the name and the second one should be the last name.
  • When a full name is composed of three words. The first and middle words should be the first name while the third word should be the last name.

Example:-

**Full Name**    
John Paul White    
Peter Smith    
Ann Marie Brown    
Jack Black    
Sam Olaf Turner

Result:-

**First Name    Last Name**    
John Paul     White    
Peter         Smith      
Ann Marie     Brown    
Jack          Black    
Sam Olaf      Turner

I have search and found solutions that are not working as intended and would like some advice.

Best Answer

Keeping it short and simple

DECLARE @t TABLE(Fullname varchar(40))
INSERT @t VALUES('John Paul White'),('Peter Smith'),('Thomas')

SELECT
  LEFT(Fullname, LEN(Fullname) - CHARINDEX(' ', REVERSE(FullName))) FirstName,
  STUFF(RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName))),1,1,'') LastName
FROM
  @t

Result:

FirstName  LastName
John Paul  White
Peter      Smith
Thomas     NULL
Related Topic