ORDER BY
    CAST(YourColumn as INT)

create table #MyTest (
    TestID varchar(10) not null,
    TestData varchar(100) not null
);

insert into #MyTest (
    TestID,
    TestData
)
select 'R000234','Some data' union all
select '100','Some more data' union all
select '101','Some more data again' union all
select '1000','And more data' union all
select 'U1023','and even more data';

select
    *
from
    #MyTest
order by
    TestID;

select
    *,
    case when left(TestID,1) like '%[A-Za-z]%'
        then left(TestID,1) + right('0000000000'  + substring(TestID,2,len(TestID) - 1), 9)
        else right('0000000000' + TestID, 10)
    end
from
    #MyTest
order by
    case when left(TestID,1) like '%[A-Za-z]%'
        then left(TestID,1) + right('0000000000'  + substring(TestID,2,len(TestID) - 1), 9)
        else right('0000000000' + TestID, 10)
    end;

drop table #MyTest;