SQL order string as number

by

If possible you should change the data type of the column to a number if you only store numbers anyway.

If you can’t do that then cast your column value to an integer explicitly with

select col from yourtable
order by cast(col as unsigned)

or implicitly for instance with a mathematical operation which forces a conversion to number 

select col from yourtable
order by col + 0

BTW MySQL converts strings from left to right. Examples:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

More Related Contents:

  1. How to find similar results and sort by similarity?
  2. How does MySQL process ORDER BY and LIMIT in a query?
  3. Ordering by the order of values in a SQL IN() clause
  4. Ordering by specific field value first
  5. MySQL “Group By” and “Order By”
  6. Using union and order by clause in mysql
  7. How do you extract a numerical value from a string in a MySQL query?
  8. MySQL order by “best match”
  9. Order by COUNT per value
  10. MySQL/MariaDB – order by inside subquery
  11. Can MySQL replace multiple characters?
  12. MySql – Way to update portion of a string?
  13. Mysql: Order by like?
  14. Sort the rows according to the order specified in WHERE IN clause
  15. Strip last two characters of a column in MySQL
  16. ORDER BY alphabet first then follow by number
  17. MYSQL Select One Random record from each Category
  18. Delete the entire content of all table in a Database [closed]
  19. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  20. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  21. How do I create a sequence in MySQL?
  22. MySQL search and replace some text in a field
  23. Group mysql query by 15 min intervals
  24. Using Count to find the number of occurrences
  25. Is there any reason to worry about the column order in a table?
  26. Checking a table for time overlap?
  27. How to fix double-encoded UTF8 characters (in an utf-8 table)
  28. MySQL INSERT INTO … VALUES and SELECT
  29. MYSQL select DISTINCT values in two columns
  30. Optimistic locking in MySQL

Leave a Comment