MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.22


create table test (val varchar(50));

insert into test values
("ZZZ-AA1"),("ZZZ-AA11"),("ZZZ-AA12"),("ZZZ-AAA1"),("ZZZ-AA2"),("ZZZ-AA3"),
("ZZZ-AB1"),("ZZZ-AB2"),("ZZZ-BB1"),("ZZZ-BB12"),("ZZZ-CA1");
select *, 
      substring_index(substring_index(val,'-',2),'-',-1) a, 
      abs(regexp_replace(val, "[A-Za-z]", "")) b,
      regexp_replace(val, "[0-9]", "") c from test
      order by c asc, b asc


val
a
b
c


ZZZ-AA1
AA1
1
ZZZ-AA

ZZZ-AA2
AA2
2
ZZZ-AA

ZZZ-AA3
AA3
3
ZZZ-AA

ZZZ-AA11
AA11
11
ZZZ-AA

ZZZ-AA12
AA12
12
ZZZ-AA

ZZZ-AAA1
AAA1
1
ZZZ-AAA

ZZZ-AB1
AB1
1
ZZZ-AB

ZZZ-AB2
AB2
2
ZZZ-AB

ZZZ-BB1
BB1
1
ZZZ-BB

ZZZ-BB12
BB12
12
ZZZ-BB

ZZZ-CA1
CA1
1
ZZZ-CA


select * from test
      order by 
      regexp_replace(val, "[0-9]", "") asc,
      abs(regexp_replace(val, "[A-Za-z]", "")) asc


val


ZZZ-AA1

ZZZ-AA2

ZZZ-AA3

ZZZ-AA11

ZZZ-AA12

ZZZ-AAA1

ZZZ-AB1

ZZZ-AB2

ZZZ-BB1

ZZZ-BB12

ZZZ-CA1


select val, lv, substring(val,1,lv-1) avv, substring(val,lv)+0 lvv from 
(select val, 
     least(if(locate(0, val)=0,999,locate(0, val)), 
     if(locate(1, val)=0,999,locate(1, val)),  
     if(locate(2, val)=0,999,locate(2, val)), 
     if(locate(3, val)=0,999,locate(3, val)),  
     if(locate(4, val)=0,999,locate(4, val)), 
     if(locate(5, val)=0,999,locate(5, val)),  
     if(locate(6, val)=0,999,locate(6, val)), 
     if(locate(7, val)=0,999,locate(7, val)),  
     if(locate(8, val)=0,999,locate(8, val)), 
     if(locate(9, val)=0,999,locate(9, val))) lv
from test) t
order by avv asc, lvv asc


val
lv
avv
lvv


ZZZ-AA1
7
ZZZ-AA
1

ZZZ-AA2
7
ZZZ-AA
2

ZZZ-AA3
7
ZZZ-AA
3

ZZZ-AA11
7
ZZZ-AA
11

ZZZ-AA12
7
ZZZ-AA
12

ZZZ-AAA1
8
ZZZ-AAA
1

ZZZ-AB1
7
ZZZ-AB
1

ZZZ-AB2
7
ZZZ-AB
2

ZZZ-BB1
7
ZZZ-BB
1

ZZZ-BB12
7
ZZZ-BB
12

ZZZ-CA1
7
ZZZ-CA
1


select val from 
(select val, 
     least(if(locate(0, val)=0,999,locate(0, val)), 
     if(locate(1, val)=0,999,locate(1, val)),  
     if(locate(2, val)=0,999,locate(2, val)), 
     if(locate(3, val)=0,999,locate(3, val)),  
     if(locate(4, val)=0,999,locate(4, val)), 
     if(locate(5, val)=0,999,locate(5, val)),  
     if(locate(6, val)=0,999,locate(6, val)), 
     if(locate(7, val)=0,999,locate(7, val)),  
     if(locate(8, val)=0,999,locate(8, val)), 
     if(locate(9, val)=0,999,locate(9, val))) lv
from test) t
order by substring(val,1,lv-1) asc, substring(val,lv)+0 asc


val


ZZZ-AA1

ZZZ-AA2

ZZZ-AA3

ZZZ-AA11

ZZZ-AA12

ZZZ-AAA1

ZZZ-AB1

ZZZ-AB2

ZZZ-BB1

ZZZ-BB12

ZZZ-CA1

val	a	b	c
ZZZ-AA1	AA1	1	ZZZ-AA
ZZZ-AA2	AA2	2	ZZZ-AA
ZZZ-AA3	AA3	3	ZZZ-AA
ZZZ-AA11	AA11	11	ZZZ-AA
ZZZ-AA12	AA12	12	ZZZ-AA
ZZZ-AAA1	AAA1	1	ZZZ-AAA
ZZZ-AB1	AB1	1	ZZZ-AB
ZZZ-AB2	AB2	2	ZZZ-AB
ZZZ-BB1	BB1	1	ZZZ-BB
ZZZ-BB12	BB12	12	ZZZ-BB
ZZZ-CA1	CA1	1	ZZZ-CA