MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table NameTable (
  ID int, 
  FullName varchar(100), 
  age int, 
  primary key(ID) );

insert into NameTable (ID, FullName, age) values
  (1, 'ben thompson', 23),
  (2, 'Martin Luther King', 23);
  








Records: 2  Duplicates: 0  Warnings: 0


select * from NameTable;


ID
FullName
age


1
ben thompson
23

2
Martin Luther King
23


SELECT SUBSTRING_INDEX(TRIM(FullName), ' ', -1) LastName,
       SUBSTRING_INDEX(TRIM(FullName), ' ', 1) FirstName,
       SUBSTR(FullName, LOCATE(' ',FullName)+1,  (CHAR_LENGTH(FullName) - LOCATE(' ',REVERSE(FullName)) - LOCATE(' ',FullName)))  AS MiddleName   
FROM NameTable;


LastName
FirstName
MiddleName


thompson
ben


King
Martin
Luther


SET autocommit=0; 
LOCK TABLES NameTable WRITE; 
alter table NameTable add column FullNameReverseOrder varchar(100) after FullName;
COMMIT; 
UNLOCK TABLES;

Records: 0  Duplicates: 0  Warnings: 0


update NameTable
set FullNameReverseOrder = concat_ws(' ' ,SUBSTRING_INDEX(TRIM(FullName), ' ', -1),
                                  SUBSTR(FullName, LOCATE(' ',FullName)+1,  (CHAR_LENGTH(FullName) - LOCATE(' ',REVERSE(FullName)) - LOCATE(' ',FullName))),
                                  SUBSTRING_INDEX(TRIM(FullName), ' ', 1) );

Rows matched: 2  Changed: 2  Warnings: 0


select * from NameTable;


ID
FullName
FullNameReverseOrder
age


1
ben thompson
thompson  ben
23

2
Martin Luther King
King Luther Martin
23


CREATE TRIGGER FullNameReverseOrderUpdate BEFORE INSERT ON NameTable
FOR EACH ROW BEGIN

SET new.FullNameReverseOrder = (concat_ws(' ' ,SUBSTRING_INDEX(TRIM(new.FullName), ' ', -1),
                                  SUBSTR(new.FullName, LOCATE(' ',new.FullName)+1,  (CHAR_LENGTH(new.FullName) - LOCATE(' ',REVERSE(new.FullName)) - LOCATE(' ',new.FullName))),SUBSTRING_INDEX(TRIM(new.FullName), ' ', 1) ));
END;
insert into NameTable (ID, FullName, age) values
  (3, 'Arthur  Thompson', 23),
  (4, 'Arthur Ben Thompson', 23);

Records: 2  Duplicates: 0  Warnings: 0


select * from NameTable;


ID
FullName
FullNameReverseOrder
age


1
ben thompson
thompson  ben
23

2
Martin Luther King
King Luther Martin
23

3
Arthur  Thompson
Thompson  Arthur
23

4
Arthur Ben Thompson
Thompson Ben Arthur
23
ID	FullName	FullNameReverseOrder	age
1	ben thompson	thompson ben	23
2	Martin Luther King	King Luther Martin	23