sql - how to split fullname into first and last name

Question

If I have a table with a column that contains fullnames such as :

fullname
------------
Joe Bloggs
Peter Smith
Mary Jones and Liz Stone

How can I retried the first and last name from each of the entries in the full name column using SQL. I'm not worried about the second name in the 3rd entry in my example i.e. Liz Stone.

So basically to retrieve

Firstname
---------
Joe
Peter
Mary

Lastname  
--------
Bloggs 
Smith
Jones

Answer 1

Here is a pre SQL Server 2016 method, which uses basic string functions to isolate the first and last names.

SELECT SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) AS Firstname,     
       SUBSTRING(fullname,
                 CHARINDEX(' ', fullname) + 1,
                 LEN(fullname) - CHARINDEX(' ', fullname)) AS Lastname
FROM yourTable

Note that this solution assumes that the fullname column only contains a single first name and a single last name (i.e. no middle names, initials, etc.).

Answer 2

I use this query to retrive first and lastname

SELECT SUBSTRING(FULLNAME, 1, CASE WHEN CHARINDEX(' ', FULLNAME)>0 THEN CHARINDEX(' ', FULLNAME) - 1 ELSE LEN(FULLNAME) END ) AS Firstname,
  REVERSE(SUBSTRING(REVERSE(FULLNAME), 1, CASE WHEN CHARINDEX(' ', REVERSE(FULLNAME))>0 THEN CHARINDEX(' ', REVERSE(FULLNAME)) - 1 ELSE LEN(REVERSE(FULLNAME)) END ) )AS Firstname FROM  HRMDESFO.EMPLOID

Resuls

Answer 3

SELECT CASE
           WHEN CHARINDEX(' ', FullName) > 0
           THEN SUBSTRING(FullName, 1, LEN(FullName) - CHARINDEX(' ', REVERSE(FullName)))
           ELSE ''
       END AS FirstName,
       CASE
           WHEN CHARINDEX(' ', FullName) > 0
           THEN REVERSE(SUBSTRING(REVERSE(FullName), 
                       1, 
                       CHARINDEX(' ', REVERSE(FullName)) - 1))
           ELSE FullName
       END AS LastName
FROM(VALUES('Mary Anne Bloggs'), ('Joe Bloggs'), ('Bloggs')) AS T(FullName);

This version checks that there is a space in the full name to split on. If there isn't then the first name is set to an empty string and the full name is put into the surname. Also, reverse is employed to split on the last space when there is more than one space