Order MySQL Text or Varchar Value Numerically

January 6th, 2013 - Posted by Steve Marks to MySQL, Web Development.

Let us begin by taking the following MySQL table:

CREATE TABLE `my_table` (
`my_value` VARCHAR( 255 ) NOT NULL

And now let’s insert the following values:

INSERT INTO `my_table` (`my_value`) VALUES ('1'), ('2'), ('5'), ('5A'), ('10'), ('30');

So we’ve got a simple table with one field of type varchar that contains 6 entries.

The Problem

Let’s imagine that we want to get the values of this table in a query ordered numerically. Ordinarily you’d probably think to try the following:

FROM `my_table`
ORDER BY `my_value`

But this will give us the following resultset:

1, 10, 2, 30, 5, 5A

The Solution

In order to get the values in a numerical order we can change our query to read as follows:

FROM `my_table`
ORDER BY (my_value * 1)

Notice how we’re multiplying the column we want to order by 1. This in essence converts the value to a number and therefore allows us to order it numerically.

The result we get now is:

1, 2, 5, 5A, 10, 30

This entry was posted on Sunday, January 6th, 2013 at 4:52 pm by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.

Fear not, we won't publish this

Comments (0)

No comments have been left yet. Be the first