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:[EMAIL PROTECTED]]
> 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.
-----------------------------------------------------------------------
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
