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.

Explicit Ordering

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 to_number();

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);