ORDER BY
case when len(a.ProdNo)>0 and charindex('-',reverse(a.ProdNo))-1>0
then
case when PATINDEX('%[A-Za-z]%',right(a.ProdNo, charindex('-',reverse(a.ProdNo))-1))=0
then convert(int,right(a.ProdNo, charindex('-',reverse(a.ProdNo))-1))
end
end