Sorting a varchar column as a numeric one using collate

Question

I have a MySQL table that has a VARCHAR column. The column contains only numbers and I want to be able to sort it numerically, i.e. as 100, 101, 1001. Currently it sorts them as 100, 1001, 101 etc. I heard i could simply alter the table to add a collate attribute so that it will be sorted numerically. When i type "show collation" i don't see any collation type that seems to be meant for treating a varchar column as a numeric one.

So my question is, what collation can I use that will sort this varchar column numerically?

score 2 · Answer 1 · answered Apr 18 '13 at 19:18

2

If this varchar column only contains numbers, then it should be an integer column. Otherwise, you can do

ORDER BY CAST(col AS SIGNED)

See the demo

answered Apr 18 '13 at 19:18

Kermit

33,827
13
85
121

score 0 · Answer 2 · answered Apr 18 '13 at 19:25

There is no built-in collation which would compare numeric values as numeric.

You can develop your own one, however, you will have hard time deploying it on sharing hostings and similar things.

If you are OK with not using an index for sorting, you may just cast it to an integer:

SELECT  *
FROM    mytable
ORDER BY
        CASE field REGEXP '^-?[0-9]$' THEN CAST(field AS SIGNED INTEGER) END

Sorting a varchar column as a numeric one using collate

2 Answers2