James, it is possible, if your number is always in the same relative
position in the string (it is in the sample data you posted below).
If it moves around a lot, you may be better off establishing some kind
of sortorder column and populating it with your favorite scripting
language. Actually that could conceivably be done in SQL too.

Here's a sample of one way to do it, using substrings based on ' '
(space) characters, casting the result to an unsigned int, then
sorting on that column.

SELECT dataline,
CAST( SUBSTRING_INDEX( SUBSTRING_INDEX(dataline, ' ', 3), ' ', -1) AS
UNSIGNED) AS nr
FROM test
ORDER BY nr;

HTH,
Dan

On 10/3/06, James Eaton <jj@zolx.com> wrote:
> If I have the following strings in a varchar column:
>
> Piano Sonata 1 - Brendel
> Piano Sonata 10 - Brendel
> Piano Sonata 11 - Brendel
> Piano Sonata 12 - Brendel
> Piano Sonata 13 - Brendel
> Piano Sonata 14 - Brendel
> Piano Sonata 15 - Brendel
> Piano Sonata 16 - Brendel
> Piano Sonata 17 - Brendel
> Piano Sonata 18 - Brendel
> Piano Sonata 19 - Brendel
> Piano Sonata 2 - Brendel
> Piano Sonata 20 - Brendel
> Piano Sonata 21 (Waldstein) - Brendel
>
> Is it possible to sort the records into the following order?
>
> Piano Sonata 1 - Brendel
>
> Piano Sonata 2 - Brendel
> Piano Sonata 10 - Brendel
> Piano Sonata 11 - Brendel
> Piano Sonata 12 - Brendel
> Piano Sonata 13 - Brendel
> Piano Sonata 14 - Brendel
> Piano Sonata 15 - Brendel
> Piano Sonata 16 - Brendel
> Piano Sonata 17 - Brendel
> Piano Sonata 18 - Brendel
> Piano Sonata 19 - Brendel
> Piano Sonata 20 - Brendel
> Piano Sonata 21 (Waldstein) - Brendel
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>