Order MySQL Text or Varchar Value Numerically

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:

SELECT *
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:

SELECT *
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