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

MySQL Query to Change Name Order in Single Column

I have a database with multiple columns, but I have been tasked with ordering the data by first name.

Issue is that both the last name and first name reside in the same column, titled ‘owner’, and I’ve been asked to not create extra columns to split the values.

For instance, the data looks something like this, with columns

 id | owner               | animal
  1 | Rogers, Zachary     | Dog
  2 | Guttierez, Rob      | Cat
  3 | Peters, Melissa     | Bird
  4 | Angelou, Bernadette | Dog

How can I take "last name, first name" and make it "first name last name" so I can order it? At the end, I’d like the data to look like this:

  1 | Zachary Rogers     | Dog
  2 | Rob Guttierez      | Cat
  3 | Melissa Peters     | Bird
  4 | Bernadette Angelou | Dog

I can handle the ORDER BY owner ASC; query after the formatting is good.

TIA!

Tags:

2

Answers


  1. 0

    Your going to need to split owner using SUBSTRING_INDEX function. This example gets the last split index and sorts on that.

    SELECT * FROM table ORDER BY SUBSTRING_INDEX(owner, ' ', -1) ASC;
    Login or Signup to reply.
  2. 0

    SUBSTRING_INDEX with the delimiter ‘,’ will give you the chance to sortby surname

    Still on the long run normalize your table, so that you animals in a separate table

    also saving surname in a separate columns would save time as string functions will take time, better spent#

    CREATE TABLE mytable
    (`id` int, `owner` varchar(19), `animal` varchar(4))
;
    
INSERT INTO mytable
    (`id`, `owner`, `animal`)
VALUES
    (1, 'Rogers, Zachary', 'Dog'),
    (2, 'Guttierez, Rob', 'Cat'),
    (3, 'Peters, Melissa', 'Bird'),
    (4, 'Angelou, Bernadette', 'Dog')
;


    Records: 4  Duplicates: 0  Warnings: 0

    SELECT id
  , CONCAT(SUBSTRING_INDEX(`owner`,',', -1),' ', SUBSTRING_INDEX(`owner`,',', 1)) sortname  
  ,`animal` 
  FROM mytable
ORDER BY sortname  DESC
    id sortname animal
    1  Zachary Rogers Dog
    2  Rob Guttierez Cat
    3  Melissa Peters Bird
    4  Bernadette Angelou Dog

    fiddle

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

Back To Top
Search