How to Sort VARCHAR Fields Numerically in MySQL?

How to Order VARCHAR Fields Numerically in MySQL

In MySQL, sorting fields of type VARCHAR lexicographically can result in undesired ordering when the values represent integers with leading zeros. This is because lexicographic ordering treats "42" as greater than "9".

To sort such fields numerically, you can use the following method:

SELECT * FROM table_name ORDER BY CAST(field_name AS SIGNED INTEGER) ASC

By casting the VARCHAR field to a SIGNED INTEGER, MySQL will interpret it as a numeric value and order the results accordingly. This ensures that "9" will appear before "42" in the sorted output.

The above is the detailed content of How to Sort VARCHAR Fields Numerically in MySQL?. For more information, please follow other related articles on the PHP Chinese website!