Its been a long time since I have posted, but here we go
After lots of messing about I finally found a solution the for the problem of ordering using an example someone else provided as well a little bit of tinkering.
Results BEFORE:
CH10
CH1001
CH400
CH50
G99
Results AFTER:
catno
CH10
CH50
CH400
CH1001
G99
Example
SELECT * FROM mycats ORDER BY left(catno,2), RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST(catno AS VARCHAR(MAX)))), 1000)
