Re: Sorting numerically within a varchar

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
>
>