How to Order a SQL String Column By Its Numeric Value
Suppose we have a
VARCHAR column in SQL that holds numbers:
"1", "3", 2".
Because the numbers are stored as strings, the
ORDER BY clause does not order by the numeric value of the string.
Ideally, we can change the data type to be a number; however, that’s not always possible.
There are two main ways to order a SQL string column by its numeric value.
We can explicitly cast the column value to a number.
SELECT col_name FROM table_name ORDER BY cast(col_name AS unsigned); -- signed works too
In Oracle SQL, we can cast using
SELECT col_name FROM table_name ORDER BY to_number(col_name);
Or we can implicitly cast it to a number using some mathematical operation that will force the conversion to a number.
Any operation that will maintain the identity and value of the original number is acceptable.
We can add zero.
SELECT col_name FROM table_name ORDER BY col_name + 0;
We can multiply by one.
SELECT col_name FROM table_name ORDER BY col_name * 1;
We can take the absolute value.
SELECT col_name FROM table_name ORDER BY ABS(col_name);