sql - MySQL - sort by certain last string character -

i'm trying sort character on string, example,

before:

+----+---------+ | id |  name   | +----+---------+ | 1  | red     | | 2  | red-a   | | 3  | red-xy  | | 4  | blue    | | 5  | blue-a  | | 6  | blue-xy | +----+---------+ 

after:

+----+---------+ | id |  name   | +----+---------+ | 4  | blue    | | 1  | red     | | 5  | blue-a  | | 2  | red-a   | | 6  | blue-xy | | 3  | red-xy  | +----+---------+ 

are there ways categorize based on -a or -xy using order by

thank in advance.

select    case      when right(`name`,length(`name`)-instr(`name`,'-')) = `name` ''     else right(`name`,length(`name`)-instr(`name`,'-'))    end `suffix`,   `name`    `table1` order   `suffix`, `name` 

if no suffix found, put record in first row set. caveat: first dash used separate word prefix.