Numeric Sorting of Varchar Strings in MySQL
When managing data in a relational database, it's often necessary to sort values based on their numeric content. However, if numeric values are stored as strings in a VARCHAR column, MySQL may not perform the sorting correctly. This article explores a solution to sort VARCHAR columns containing numbers as true numeric values.
Problem:
How can we sort a column of type VARCHAR that stores numeric values as strings, such as '17.95', '199.95', and '139.95', as actual numbers in MySQL?
Solution:
To sort VARCHAR strings containing numbers as numeric values, we can use the following technique:
<code class="sql">SELECT * FROM tbl ORDER BY string_column * 1</code>
This operation casts the VARCHAR string to a numeric data type, allowing MySQL to perform the sorting correctly.
Benefits of using * 1:
Beyond enabling numeric sorting of VARCHAR strings, multiplying by 1 offers several other advantages:
The above is the detailed content of How can I sort VARCHAR columns containing numbers in MySQL as true numeric values?. For more information, please follow other related articles on the PHP Chinese website!