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.

Adam Hopkinson · Accepted Answer · 2010-04-15 11:33:59Z

6

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

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

Edit sorry, fixed syntax

answered Apr 15, 2010 at 11:33

Adam Hopkinson

28.8k9 gold badges74 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karthik Over a year ago

Thanks for your answer, In mysql cast function integer have problem, so i used signed. now working. ORDER BY CAST(receiptno AS SIGNED) ASC - working properly

Your Common Sense · Accepted Answer · 2010-04-15 11:37:29Z

1

So, change the type of this column

answered Apr 15, 2010 at 11:37

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Collectives™ on Stack Overflow

How do I sort a varchar column using numeric ordering

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related