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


Improve confidence and job performance

Improve productivity and efficiency

Learn more,
earn more

Life-long
investment
To learn more about this topic, we are providing you with recommendations to help you further your knowledge. These are our affiliate links to Amazon where you can purchase them and also explore a variety of other relevant books.