tsql - 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.).