By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE persons (
fullname VARCHAR(100)
);
INSERT INTO persons VALUES
('John'),
('Godfrey Roach'),
('Merritt Lenard Durant'),
('Annabelle Pris Johns'),
('Donovan Branden Bourke'),
('Xavier Denice Wells'),
('Madeline V. Foster'),
('Cedric Dex S.'),
('M. Danica Dorsey'),
('Mary J. Smith');
10 rows affected
SELECT
LTRIM(RTRIM(LEFT(fullname, CHARINDEX(' ', fullname)))) AS first_name,
LTRIM(RTRIM(RIGHT(fullname, CHARINDEX(' ', REVERSE(fullname))))) AS last_name,
LTRIM(RTRIM(CASE WHEN PATINDEX('%_ %_% _%', fullname) > 0 THEN SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, (CHARINDEX(' ', fullname, CHARINDEX(' ', fullname)+1)-(CHARINDEX(' ', fullname) + 1))) ELSE '' END)) AS middle_name
FROM persons
| first_name | last_name | middle_name |
|---|---|---|
| Godfrey | Roach | |
| Merritt | Durant | Lenard |
| Annabelle | Johns | Pris |
| Donovan | Bourke | Branden |
| Xavier | Wells | Denice |
| Madeline | Foster | V. |
| Cedric | S. | Dex |
| M. | Dorsey | Danica |
| Mary | Smith | J. |
SELECT REPLACE(REPLACE(REPLACE(last_name + ' ' + middle_name + ' ' + first_name, ' ', '<>'), '><', ''), '<>', ' ') FROM (
SELECT
LTRIM(RTRIM(LEFT(fullname, CHARINDEX(' ', fullname)))) AS first_name,
LTRIM(RTRIM(RIGHT(fullname, CHARINDEX(' ', REVERSE(fullname))))) AS last_name,
LTRIM(RTRIM(CASE WHEN PATINDEX('%_ %_% _%', fullname) > 0 THEN SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, (CHARINDEX(' ', fullname, CHARINDEX(' ', fullname)+1)-(CHARINDEX(' ', fullname) + 1))) ELSE '' END)) AS middle_name
FROM persons
)t
| (No column name) |
|---|
| Roach Godfrey |
| Durant Lenard Merritt |
| Johns Pris Annabelle |
| Bourke Branden Donovan |
| Wells Denice Xavier |
| Foster V. Madeline |
| S. Dex Cedric |
| Dorsey Danica M. |
| Smith J. Mary |