Sort Text Column Numerically in MySQL

MySQL LogoNormally 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

2 thoughts on “Sort Text Column Numerically in MySQL

  1. Sean says:

    Hi,

    I have a field that contains 3 characters for instance number then d as in day, h as in hour and m as in minutes eg:-

    0d 19h 32m

    using your query works in a fashion but as you can see it matches on the 1st “field” (0d) and does not set any order for the remaining fileds

    0d 19h 32m
    0d 0h 0m
    4d 11h 39m
    4d 20h 31m
    4d 20h 33m
    4d 20h 31m
    6d 2h 1m
    8d 18h 57m
    177d 2h 20m
    195d 18h 47m

    the command used is as detailed below:-

    SELECT * FROM hosts WHERE host_status = ‘HOSTDOWN’ AND acked != ‘1’ ORDER BY `hosts`.`host_time`+0 ASC

    thanks

    Sean

  2. learn more says:

    Hardwood home furniture has one thing really all natural concerning it.
    There is this sense of coziness, of nature as well as of style that may be
    be discovered in wood furniture. Hardwood is born from the planet.

Leave a Reply

Your email address will not be published. Required fields are marked *