Re: Sorting numerically within a varchar

James,
That wasn't too easy to figure out. But this will work:

select * from Table1 order by
substring_index(Music_Title,' ',2), -- Extracts first 2 words
0+Substring_Index(Substring_index(Music_Title,'-',1),' ',-2), --
Extracts the number
substring_index(Music_Title,' ',-1) -- Extracts the last word
Mike



At 02:42 PM 10/3/2006, you 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=mos99@fastmail.fm