Home > Resources > Articles > Programming > PHP > How to Sort or Order Numerical Varchar Data (MySQL)

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

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 below:

mysql> SELECT column FROM table_name ORDER BY column;

column
======
100
1000
10000
200
2000
20000
...

Because the data value is numerical, 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 fix instead of sorting to CAST operator.


Suggested Reading

Essential PHP Security

Amazon Reviewer:
Essential for the Beginner or Advanced PHP developer
(Amazon Affiliate Link)