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 |