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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
