>SELECT PADL(TRIM(emNumber), 99) From Employees >>It 's supposed to order a character value as if it is a numeric field, but the values can also contain characters to a conversion to numeric would not do it.
select right(space(20) + RTRIM(emNumber), 20) as result from @tmp order by 1;