By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select t.str
from (values ('1-ABC'),
('12-ABC'),
('2-ABCD'),
('ABC-10'),
('ABC-100'),
('ABC-100A'),
('ABC-11')
) t(str) cross apply
(values (left(str, charindex('-', str + '-') - 1), stuff(str, 1, charindex('-', str), ''))
) v(part1, part2)
order by coalesce(try_convert(int, v.part1), 999999999),
part1,
try_convert(int, left(v.part2, patindex('%[^0-9]%', v.part2 + 'x') - 1)),
part2
| str |
|---|
| 1-ABC |
| 2-ABCD |
| 12-ABC |
| ABC-10 |
| ABC-11 |
| ABC-100 |
| ABC-100A |