Normally when you want to sort numerically on a column in your database you’d make the column some sort of numeric type such as an int. Sometimes, however, you are stuck with someone else’s schema and they have decided to store numbers in a text type column and you need to sort your results numerically because an alphabetic sort on a number does not produce the results you want. There is a quick trick to that makes this easy.

The Solution

Put a +0 after the column name in your Order By clause like this.

  SELECT ColumnName 
  FROM TableName 
  ORDER BY TextColumnName+0 ASC;

Real Life Example

Suppose you are working in WordPress with a custom post type that stores a numeric value like “price” in the WordPress postmeta table in the meta_value column which happens to be of type LONG_TEXT. Here is a query that will let you select all the custom posts and sort them by price in descending order.

  SELECT posts.*, postmeta.meta_value as price
  FROM $wpdb->posts posts, $wpdb->postmeta postmeta
  WHERE posts.ID = postmeta.post_id 
  AND postmeta.meta_key = 'price' 
  AND posts.post_type = 'product' 
  ORDER BY postmeta.meta_value+0 DESC