Sort Text Column Numerically in MySQL

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.

1 2 3

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.

1 2 3 4 5 6

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