> 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
