MySQL, add column 'FullNameReverseOrder' with data from fullname column and change (FName, LName) to (LName, FName)

Question

I want to add a new column named FullNameReverseOrder to a table named NameTable, where the information of FullName is arranged in the order of FirstName LastName, and the FullNameReverseOrder will be saved in the order of LastName FirstName.

Here is a table you can use:

create table NameTable (ID int, FullName varchar(100), age int, primary key(ID));
insert into NameTable (ID, FullName, age) values(1, 'ben thompson', 23);

Add a new column named FullNameReverseOrder:
alter table NameTable add column FullNameReverseOrder varchar(100) ...don't know what to do here... after FullName;

Answer 1

Some points to consider.

• Do not store age, because it changes every year and the table needs to be updated to use date of birth instead.
• If available, use separate columns to store first, last, and middle names.

Based on the question

Consider the following data example where the FullName column consists of up to three words separated by spaces

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);

Inquire,

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;

result,

LastName    FirstName   MiddleName
Thompson      Ben   
King          Martin      Luther

First make changes by modifying the table structure. If there are a large number of transactions, I recommend locking the table appropriately

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

To update the newly added columns LastName, MiddleName and FirstName, use the following command:

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) );

choose,

select * 
from NameTable;

result

ID  FullName             FullNameReverseOrder      age
1   ben thompson          thompson  ben            23
2   Martin Luther King    King Luther Martin       23

Now, if you want this process to happen automatically, consider creating a trigger.

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 test value

insert into NameTable (ID, FullName, age) values
  (3, 'Arthur  Thompson', 23);

select * from NameTable;

result

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

View example