Hi.
On Tue, Sep 25, 2001 at 09:58:44AM -0700, [EMAIL PROTECTED] wrote:
> Hello
>
> I have a table that I want to sort.
> I want to sort the table according to a text field
> that might contain text or numbers
> The sort results sould look like this:
>
> 50
> 100
> aaa
> bbb
>
> I want the numbers to sort like numbers and text like
> text.
SELECT *
FROM table1
WHERE ...
ORDER BY IF( mixed_column+0 = 0, mixed_column, LPAD( mixed_column, 11, '0' ) )
It's ugly, but it should work. You need a 3.23.x version for ORDER BY
to work on expressions. It doesn't work on negative numbers, though.
Of course, you could drop the IF and only use LPAD, but then the
padding must be longer than the longest possible text, which could be
large and therefore slow down the query considerably.
>
> I cannot split the content of the text field to a text
> field and a numbers field.
Nevertheless, it seems that this column is somehow broken. Could you
fill in leading '0'? This would make the number to "behave correctly"
when sorted as text.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
