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