>I have a varchar column containing entries that I want to sort. Each
>entry consists of a combination of letters, and an optional number.
Your examples violate this, in that they seem to contain spaces, which
are neither letters nor numbers.
>Example:
>aaa
>aaa 2
>aaa 4
>aaa 12
>bbb
>bbb 4
>ccc 1
>ccc 2
>ddd
>eee
You can ORDER BY an expression, or several of them. Your examples
don't give the full generality of the data, but let's try to do it anyway:
left(str, 4) is the alpha part (assuming it's always 4 characters long
including the space)
substr(str, 5) is the numeric part as a string.
(int)substr(str, 5) is the numeric part as a number.
so try:
ORDER BY left(str,4), (int)substr(str,5)
(order by the alpha part, then break ties with the numeric part,
treated as a number)
This does NOT handle the case of no numeric part. It also assumes that
the alpha part is fixed-length. If you can assume there's a space in there,
you can use locate() to find the position of the space. Stuff like if() or
ifnull() could handle the case where the numeric part comes out a zero-length
string or NULL.
I think you can handle the no-numeric-part issue with
if(substr(str,5) = '', '0', substr(str, 5)) as the numeric part as
a string, defaulting to '0' if it isn't there.
(int)if(substr(str, 5) = '', '0', substr(str, 5)) is the numeric
part as a number.
|
