SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.employees ( fullname varchar(100), firstname varchar(50), lastname varchar(50) ) ;

INSERT INTO employees (fullname )
VALUES ('McCartney, Paul'),('Lennon, John'),('Harrison, George'),('Starr, Ringo'),
('Smith, John, Jr.')
;
5 rows affected

SELECT * FROM dbo.employees


fullname
firstname
lastname


McCartney, Paul
null
null

Lennon, John
null
null

Harrison, George
null
null

Starr, Ringo
null
null

Smith, John, Jr.
null
null


SELECT fullname
  , ltrim(rtrim(left(fullname,charindex(',',fullname+',')-1))) as lastname
  , ltrim(rtrim(substring(fullname,charindex(',',fullname+',')+1,len(fullname)))) as firstname
FROM dbo.employees


fullname
lastname
firstname


McCartney, Paul
McCartney
Paul

Lennon, John
Lennon
John

Harrison, George
Harrison
George

Starr, Ringo
Starr
Ringo

Smith, John, Jr.
Smith
John, Jr.

fullname	firstname	lastname
McCartney, Paul	null	null
Lennon, John	null	null
Harrison, George	null	null
Starr, Ringo	null	null
Smith, John, Jr.	null	null

fullname	lastname	firstname
McCartney, Paul	McCartney	Paul
Lennon, John	Lennon	John
Harrison, George	Harrison	George
Starr, Ringo	Starr	Ringo
Smith, John, Jr.	Smith	John, Jr.