How Can I Sort VARCHAR Fields Numerically in MySQL?

Sorting Varchar Fields Numerically in MySQL: The CAST Function

In certain scenarios, a MySQL varchar field may contain integer values with leading zeros, causing lexicographical sorting to deviate from the desired numerical ordering. To resolve this, the CAST function provides a solution.

Issue:

Consider a table with a field named "number" of type varchar, storing integer values. Despite its data type, "number" contains values like "009" and "042". When sorted using the following query:

SELECT * FROM table ORDER BY number ASC

The result would be:

number
009
042
9

Solution:

To sort by numerical values, MySQL provides the CAST function. By casting the varchar field to a SIGNED INTEGER, the leading zeros are ignored, resulting in correct numerical ordering.

The modified query using the CAST function:

SELECT * FROM table ORDER BY CAST(number AS SIGNED INTEGER) ASC

Will now produce the desired sorting:

number
9
009
042

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