> OK, I'm sorry. The solution I gave doesn't work.
>
> You need to have some sort of conditional in the sort expression so
that
> numbers are sorted numerically and other things are sorted >
> alphanumerically.
> I'm not aware of a test for numeric vaues in MySql, so you need to use
>
> some
> trick to differentiate between the two.
>
> This seems to work, provided there are no negative numbers and the
text
> things start with letters
>
> ORDER BY IF (cost < ':', LPAD(cost,10,'0'), CONCAT('1',cost))
>
> In this,
> cost < ':' tests if the string starts with a digit (':' is the
character
> after '9')
> LPAD(cost,10,'0') pads the integer on the left with zeros -- replace
the
> 10
> with a number at least one more than the maximum number of digits
> CONCAT('1',cost) causes the text items to sort to the end (the
numbers
> now start with '0')
>
> Pasha's solution is probably cleaner, but you have to change the
> table definition. For Pasha's solution to work, you would need to
> have the text_val column be null (or '') when the value is numeric.
You could try something like this alphanumeric sort:
order by
case
when substring(cost,1,1) between '0' and '9'
then ''
when substring(cost,2,1) between '0' and '9'
then left(cost,1)
when substring(cost,3,1) between '0' and '9'
then left(cost,2)
...
else 'ZZZZZ'
end
, cast(
case
when substring(cost,1,1) between '0' and '9'
then substring(cost,1)
when substring(cost,2,1) between '0' and '9'
then concat('0',substring(cost,2))
when substring(cost,3,1) between '0' and '9'
then concat('00',substring(cost,3))
...
else 99999
end
as integer)
-Erich-
