Colin, According to the docs this function wants a string and when I use the field name it says "unknown column". Thanks anyway.
-----Original Message----- From: Colin Kettenacker [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:32 PM To: Mysql_List (E-mail) Subject: Re: WoW!!!! SQL is something! > I had a problem that required a proper sort of the following: > > IT66 > ITD38 . . <SNIP> . . > 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. Well I'm fairly new to MySQL so I am not sure if this'll work. If it does it will save a few cycles of logic. SELECT LPAD(req_number,6,'0'); Don't know if you need to coerce the numeric data into a string first though. ck --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
