MariaDB 10.4

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

create table t as
select 'd1 this 10' as name union all
select 'd1 this 9' as name union all
select 'this item' as name
select *
from t

name

d1 this 10

d1 this 9

this item

select t.*
from t
order by (case when name regexp ' [0-9]+$'
               then left(name, length(name) - instr(reverse(name), ' '))
               else name
          end),
         length(name),
         name

name

d1 this 9

d1 this 10

this item

select t.*
from t
order by regexp_replace(name, '[0-9]*$', ''),
         length(name),
         name

name

d1 this 9

d1 this 10

this item