Re: convert varchar/char to integer

[morten bjoernsvik]

morten bjoernsvik <morten_bjoernsvik@stripped> skrev: Hi Gents
 
 Is it possible to sort char/varchars that only contains integers as integers and not as
characters.  I can't use integer because the standard says it may contain characters
aswell. Currently I do this outside mysql in a perl-procedure.
 
 
I found the answer to left pad the numbers so they all had the same amount of characters:
    mysql> select LPAD(card_acceptor_id_42,12,'00000000000000') from merchant order by
LPAD(card_acceptor_id_42,12,'0000000000000') limit 10;
 +-----------------------------------------------+
 | LPAD(card_acceptor_id_42,12,'00000000000000') |
 +-----------------------------------------------+
 | 000000000026                                  |
 | 000000000042                                  |
 | 000000000083                                  |
 | 000000000273                                  |
 | 000000000307                                  |
 | 000000000331                                  |
 | 000000000448                                  |
 | 000000000620                                  |
 | 000000000620                                  |
 | 000000000794                                  |
 +-----------------------------------------------+
 10 rows in set (0.01 sec)