SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (t_col varchar(50))
insert into test values('High_Speed')
1 rows affected

insert into test values('M1 Speed')
1 rows affected

insert into test values('M13 Speed')
1 rows affected

insert into test values('M14 Speed')
1 rows affected

insert into test values('M31 Speed')
1 rows affected

insert into test values('M2 Speed')
1 rows affected

insert into test values('M3 Speed')
1 rows affected

insert into test values('Medium_Speed')
1 rows affected

insert into test values('Test1 zone2 High_Speed')
1 rows affected

insert into test values('Test1 zone1 High_Speed')
1 rows affected

insert into test values('Test1 zone11 High_Speed')
1 rows affected

insert into test values('Test1 zone21 High_Speed')
1 rows affected

insert into test values('Zone206 Speed')
1 rows affected

select t_col from (
select t_col
       , LEFT(SUBSTRING(t_col, PATINDEX('%[a-z]%', t_col), LEN(t_col))
         , PATINDEX('%[^a-z]%', SUBSTRING(t_col, PATINDEX('%[a-z]%', t_col), LEN(t_col)))-1) col_col
       , convert(int, LEFT(SUBSTRING(t_col, PATINDEX('%[0-9]%', t_col), LEN(t_col))
         , PATINDEX('%[^0-9]%', SUBSTRING(t_col, PATINDEX('%[0-9]%', t_col), LEN(t_col)))-1)) as ord
from test) T1
order by col_col asc, ord asc, t_col asc;
 


t_col


High_Speed

M1 Speed

M2 Speed

M3 Speed

M13 Speed

M14 Speed

M31 Speed

Medium_Speed

Test1 zone1 High_Speed

Test1 zone11 High_Speed

Test1 zone2 High_Speed

Test1 zone21 High_Speed

Zone206 Speed