php - Mysql varchar fields with number not sorting well -

i have little knowledge of mysql, importing csv file many numbers in db, developer changed type of fields int varchar , script sort 1 fields numeric descending no longer display number in descending order well, numbers mix up, if use phpmyadmin , click on column.

he mentioned me when changed numerical fields value varchar because data getting corrupted otherwise. succeeded in importing data display script not working anymore.

i way on head.

'avgsearches` varchar(11) collate utf8_bin default null, engine=innodb  default charset=utf8 collate=utf8_bin auto_increment=1406 

here sorting order:

9900 90 880 8100 74000 720 70

select distinct keyword,avgsearches traffic status='y' order avgsearches desc

one way, make fixed length strings padded left leading zeroes. prevents use of index ordering job done. neater if converted stored function.

missing 'trim'.

select distinct keyword, avgsearches  traffic  status='y'  order right(concat('00000000000', trim(avgsearches)), 11)  desc 

to confirm wrong please run query:

select distinct keyword, avgsearches,         concat('!', avgsearches, '!') raw_avgs,        length(avgsearches) avgs_raw_len,        concat('!', trim(avgsearches), '!') trim_avgs,        length(trim(avgsearches))      avgs_len,         char_length(trim(avgsearches)) avgs_char_len,         right(concat('00000000000', trim(avgsearches)), 11) with_trim traffic  status='y'  order right(concat('00000000000', trim(avgsearches)), 11)  desc 

here sample output supplied op:

keyword                 avgsearches   no_trim with__trim  window valances         9900           009900     009900  wood blinds             9900           009900     009900  arch window shutters      90         00000090   00000090  arched window shutters    90         00000090   00000090 

which cannot correct! 'no_trim' , 'with_trim' columns should 11 characters wide according query.

hmm, starting wonder in avgsearches column.

i have edited query tell me in column.