Varchar to number conversion for sorting

Question

I have a query ordered by column:

select * from mytable order by column asc — sort table

column type is varchar, so the output is:

1
10
100
11
12
13

How should I sort if I want them to sort by numeric value so the output is:

1
10
11
12
13
100

• Bozho: explain your question better. And why isn't working for you.
• mmattax: It's not working because the column's a varchar and he wants to sort by the numerical value. Wasn't that hard...

Answer 1

Use:

order by cast(column as unsigned) asc

• Zei: Can anyone explain why this AS INT doesn't work, but UNSIGNED does?

Answer 2

You can use this if you want to treat column as INT only:

SELECT * FROM mytable ORDER BY column+0;
1
10
11
12
13
100

Or this if you want to treat column as both INT and VARCHAR

SELECT * FROM mytable ORDER BY column+0, column; #this will sort the column by VARCHAR first and then sort it by INT
abc
xyz
1
10
11
12
13
100

• Macbric: If you want to sort desc : SELECT * FROM mytable ORDER BY column+0 DESC, column DESC

Answer 3

This should work as well:

order by (0 + column) asc

• Pablo Santa Cruz: Very clever. Which one do you think will perform better?
• mmattax: I don't know the performance implications of this compared to Pablo's answer. If anyone knows, I'd like to know.
• Rajiv Pingale: What is the significance of adding 0 to column ?
• FrancescoMM: Using cast, the MySQL engine knows you want to convert to integer for sorting. Using addition, MySQL thinks you need to perform some generic calculations ( same as -1/sqrt(column)*log column^2 ) before sorting, while you just need a simple conversion. So I guess the first option is faster, as it can optimize exactly for what you need.
• FrancescoMM: @RajivPingale adding 0 to a string converts the string to an integer: "123"+0=123 thus the result is integer and sorted as such, so 10 goes after 2, while "10" is before "2" (alphabetically)

Answer 4

If we simply modify the order by declaration slightly (add "+0′′ to the order by field), you can force MySQL to sort the field naturally.

 > select * from mytable order by column+0 asc;
 column
 1 
 10
 11
 12
 13 
 100

Answer 5

 Added a full code script here , but need to sort 1001 and 1002 before - as well.
We have total 5 solution , means 5 different queries as solution with full script.
=============================================================
 SET NAMES utf8;
 SET foreign_key_checks = 0;
 SET time_zone = 'SYSTEM';
 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
 DROP TABLE IF EXISTS `varchar_sort`;
 CREATE TABLE `varchar_sort` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `actual_user_id` varchar(200) DEFAULT NULL,
 PRIMARY KEY (`user_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 INSERT INTO `varchar_sort` (`user_id`, `actual_user_id`) VALUES
 (1, '1001-4'),
 (2, '1001-1'),
 (3, '1001-111'),
 (4, '1002-1'),
 (5, '1001-66'),
 (6, '1001-100'),
 (7, '1001-110');
 SELECT user_id,actual_user_id,CONVERT(SUBSTRING_INDEX(actual_user_id,'-',-1),UNSIGNED INTEGER) AS num
 FROM varchar_sort
 ORDER BY num;
 SELECT user_id,actual_user_id
 FROM varchar_sort
 ORDER BY CONVERT(SUBSTRING(actual_user_id, 6), SIGNED INTEGER);
 SELECT user_id,actual_user_id
 FROM varchar_sort
 ORDER BY CONVERT(SUBSTRING(actual_user_id, LOCATE('-', actual_user_id) + 1), SIGNED INTEGER);
 SELECT *, CAST(SUBSTRING_INDEX(actual_user_id, '-', -1) AS UNSIGNED) as num FROM varchar_sort ORDER BY num;

select * from varchar_sort order by convert( replace(actual_user_id, '-',''), SIGNED INTEGER ) asc

 **Need to sort 1001 and 1002 as well.**

• thex: @SuperFamousGuy It is a good answer but it is just relevant but not targets the OPs question