Question 231626 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL, add column 'FullNameReverseOrder' using data from fullname column and changing (FName, LName) to (LName, FName)

I want to add a new column called FullNameReverseOrder to a table called NameTable where the info for the FullName is in FirstName LastName order and the FullNameReverseOrder will hold the LastName FirstName order.

this 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 the new column called FullNameReverseOrder:
alter table NameTable add column FullNameReverseOrder varchar(100) ...no idea what to do here... after FullName;
Tags:

2

Answers


  1. 0

    After adding your new column FullNameReverseOrder you could use SUBSTRING_INDEX to split on the first space to get the first name (this is an assumption that first names don’t contain spaces).

    <first name> = SUBSTRING_INDEX(FullName, ' ', 1)

    The last name is then the remainder of the FullName string after the first name (and space).

    <last name> = SUBSTRING(FullName, LENGTH(<first name>) + 1)

    You can use CONCAT to join the parts again. Putting it all together:

    UPDATE NameTable SET FullNameReverseOrder = CONCAT(
  SUBSTRING(FullName, LENGTH(SUBSTRING_INDEX(FullName, ' ', 1)) + 1),
  ' ',
  SUBSTRING_INDEX(FullName, ' ', 1)
);
    Login or Signup to reply.
  2. 0

    Some points to consider.

    • Do not store age, it will change every year and the table needs update, use date of birth instead.
    • Use separated column per FirstName , LastName and MiddleName if there is one.

    As per the question

    Consider the following data examples which consider the FullName column has maximum three words separated by space

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

    Query ,

    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 change the table structure by altering , I always suggest proper locinkg the table if there is a large number of transactions

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

    To update the new added column LastName , MiddleName, FirstName use,

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

    Select ,

    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 the process automatic, 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

    Se examplee

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search