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!

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;

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

> status
> 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

fiddle