The field content needs to be combined and sorted after splitting
postgreSql:
order by substr(PumpNo,1,1),to_number(substr(PumpNo,2, length(PumpNo)-1), '9999' )
Sql server:
order by substring(PumpNo,0,1),convert(int,substring(PumpNo,1, length(PumpNo)-1) )