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.
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:
ORDER BY `my_value`
But this will give us the following resultset:
1, 10, 2, 30, 5, 5A
In order to get the values in a numerical order we can change our query to read as follows:
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