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