List:General Discussion« Previous MessageNext Message »
From:morten bjoernsvik Date:January 20 2006 12:15pm
Subject:Re: convert varchar/char to integer
View as plain text  

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)
 
   
 

Thread
Re: convert varchar/char to integermorten bjoernsvik20 Jan