As I've often thought it would be very useful if MySQL regexp support included being able to pull out the substring matched (and do regexp substitutions). Although that could just be my perl sensibilities ;-)
mark On Tue, 2006-10-03 at 17:27 -0500, mos wrote: > 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/[EMAIL PROTECTED] > MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
