By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #Names (FullName nvarchar(100));
INSERT INTO #Names (FullName) VALUES
('Duck, Daffy'),
('Bunny, Bugs F.'),
('Cartoon Network'),
('Doo, Scooby D'),
('Cat, Sylvester The'),
('Cat,Sylvester The'),
('Cat,Sylvester The');
7 rows affected
WITH NameSplit AS
(SELECT FullName,
LTRIM(RTRIM(CASE WHEN FullName LIKE '%,%' THEN LEFT(FullName, CHARINDEX(',',FullName)-1) ELSE FullName END)) AS LastName,
LTRIM(RTRIM(CASE WHEN FullName LIKE '%,%' THEN STUFF(FullName, 1, CHARINDEX(',',FullName), '') ELSE FullName END)) AS NameRest
FROM #Names
)
SELECT FullName,
LastName,
CASE WHEN FullName LIKE '%,%' AND NameRest LIKE '% %' THEN LEFT(NameRest, CHARINDEX(' ', NameRest)-1) ELSE NameRest END AS FirstName,
CASE WHEN FullName LIKE '%,%' AND NameRest LIKE '% %' THEN LEFT(LTRIM(STUFF(NameRest, 1, CHARINDEX(' ', NameRest), '')), 1) ELSE '' END AS MiddleInitial
FROM NameSplit
| FullName | LastName | FirstName | MiddleInitial |
|---|---|---|---|
| Duck, Daffy | Duck | Daffy | |
| Bunny, Bugs F. | Bunny | Bugs | F |
| Cartoon Network | Cartoon Network | Cartoon Network | |
| Doo, Scooby D | Doo | Scooby | D |
| Cat, Sylvester The | Cat | Sylvester | T |
| Cat,Sylvester The | Cat | Sylvester | T |
| Cat,Sylvester The | Cat | Sylvester | T |