How to Sort Stringified Numbers Numerically in MySQL?

Sort string numbers by numerical value in MySQL

When dealing with numbers stored as strings in a MySQL database, sorting by numerical size can be tricky. The following questions highlight the issue:

Question:

How to sort a column containing stringified numbers in ascending order, sorting by their numeric size rather than their character representation?

Answer:

Explicitly or implicitly converting string values ​​to integers allows for correct numerical ordering:

Explicit conversion:

<code class="language-sql">SELECT col
FROM yourtable
ORDER BY CAST(col AS UNSIGNED)</code>

In this example, CAST converts the col column to an unsigned integer before sorting.

Implicit conversion:

<code class="language-sql">SELECT col
FROM yourtable
ORDER BY col + 0</code>

Adding 0 forces MySQL to interpret col as a numeric value before sorting.

Note:

MySQL interprets strings from left to right for conversion. For example:

• "1" is converted to 1.
• "ABC" converts to 0 (no leading digit).
• "123miles" converts to 123.
• "$123" converts to 0 (first character is not a number).

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