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);
More SQL Articles
- How to Modify Table to Use Composite Primary Key in MySQL
- How to Format a Number with Two Decimal Places in SQLite
- How to Order By Multiple Columns in SQL
- How to Count the Number of Rows with the Same Value
- How to Check if a Column is Null in MySQL
- How to Combine Columns Values Into a New Column in MySQL
- How to Drop Multiple Columns with ALTER TABLE in SQL
- What Stripe Data Do I Store in My Database?
- How To Query JSON Object with Unknown Keys in PostgreSQL
- How To Convert an Escaped JSON String in PostgreSQL to a JSON Object