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