How to Sort or Order Numerical Varchar Data in MySQL [Source Code]

How to Sort or Order Numerical Varchar Data in MySQL without Using the CAST Function

Here's a quick tip for sorting VARCHAR type data in MySQL databases with numerical values in a column.

With the default sort, it would look something like this:


                    mysql> SELECT column FROM table_name ORDER BY column;
                    column

                        ======

                        100

                        1000

                        10000

                        200

                        2000

                        20000

                        ...

Because the data value is text, this ordering, while correct, may not be desirable in certain situations. The following quick fix allows you to sort numerically on varchar fields without having to use CAST. This is accomplished by using "ORDER BY column+0", and the result of this is illustrated below:


                    mysql> SELECT column FROM table_name ORDER BY column+0;
                    column

                        ======

                        100

                        200

                        1000

                        2000

                        10000

                        20000

                        ...

This is a quick and easy solution to sort numbers stored as text datetype in numerical order without needing to use the CAST operator.

Recommended Reading