List:General Discussion« Previous MessageNext Message »
From:Gerben Gieling Date:December 5 2002 8:09am
Subject:RE: WoW!!!! SQL is something!
View as plain text  
Sorry,
should be the lpad function:
http://www.mysql.com/doc/en/String_functions.html

LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr until str is
len characters long. If str is longer than len' then it will be
shortened to len characters. 

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'


> -----Original Message-----
> From: Norris, Joseph [mailto:NorrisJ@stripped] 
> Sent: December 04, 2002 18:26
> To: Mysql_List (E-mail)
> Subject: WoW!!!! SQL is something!
> 
> 
> I had a problem that required a proper sort of the following:
> 
> 
>  IT66
>  ITD38
>  IT68
>  ITD39
>  ITD40
>  IT69
>  ITD55
>  ITD56
>  IT72
>  ITD57
>  ITD58
>  ITD59
>  ITD60
>  IT73
>  ITD73
>  ITD67
>  IT74
>  ITD69
>  ITD70
>  IT78
>  IT79
>  IT83
>  ITD71
>  IT81
>  ITD75
>  ITD76
>  IT84
>  ITD96
>  ITD97
>  ITD98
>  ITD99
>  ITD100
>  ITD101
>  ITD102
>  ITD103
>  ITD104
>  ITD105
> 
> 
> Because of the different lengths, this was a nightmare for 
> "order by" and so
> it led me to my first experiments with case
> and I go the following:
> 
> select req_number, case
> when (length(req_number)=4) then concat('00', req_number)
> when (length(req_number)=5) then concat('0', req_number)
> when (length(req_number)=6) then req_number
> END as sort_req
> from req order by sort_req;
> 
> Now there might be other ways to handle this and I would love 
> to hear about
> them but I thought that this taught me 
> something about sql that I had not understood until now. Just 
> would like to
> give a little back.
> 
> Thanks to all.
>
 
-----------------------------------------------------------------------
DISCLAIMER:
This message, including attachments, is confidential and may be
privileged. If you are not an intended recipient, please notify the
sender then delete and destroy the original message and all copies. You
should not copy, forward and/or disclose this message, in whole or in
part, without permission of the sender. 
-----------------------------------------------------------------------


 
Thread
WoW!!!! SQL is something!Joseph Norris4 Dec
  • Re: WoW!!!! SQL is something!Colin Kettenacker5 Dec
RE: WoW!!!! SQL is something!Gerben Gieling5 Dec
RE: WoW!!!! SQL is something!Gerben Gieling5 Dec