How do I sort a varchar column using numeric ordering

Question

I have the field receiptno, which is a varchar holding a numeric value, and want to sort this. Sorting as a string gives me the wrong ordering. I'd like to try sorting it as an integer. Is there anyway to converting to integer in the order by clause so I can sort by integer in the query itself.

Answer 1

You can use cast or convert to convert the field type:

... ORDER BY CAST(receiptno AS INTEGER) ASC

Edit sorry, fixed syntax

Answer 2

So, change the type of this column