How to Sort String Numbers in MySQL?

Sorting String Numbers in MySQL

Question:

How can I sort a column of type VARCHAR that stores numeric values as strings in MySQL? For example, I have a column containing "17.95", "199.95", and "139.95" and need to sort them as numbers.

Answer:

The most efficient and straightforward method to achieve this is by using multiplication by 1:

SELECT *
FROM tbl
ORDER BY number_as_char * 1

This method has several advantages:

• It avoids potential precision issues caused by casting.
• It can handle non-numeric data gracefully, ignoring it during sorting.
• It extracts the numeric part from alphanumeric strings, allowing for accurate sorting.

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