MySQL Order By Regexp, Possible?

[dataman911]

Hello,

I know you can use MySQL's REGEXP functions in your WHERE clause, but what about your ORDER BY clause?

My database's "album" column has rows that look like:
This Is An Album (2001)
Another Album (1986)
Last Album (1999)
etc.

I want to use MySQL's ORDER BY clause to only sort the dates (the 2001, 1986, and 1999) and not the entire row.

I believe that REGEXP might be a solution, but I don't know if you can use it in your ORDER BY clause. Does anyone have any suggestions?

Thanks for any help.

[dopey]

Not sure about using a REGEXP, as it only seems to return whether it matched or not, not what it actually matched.

As long as the brackets containing the year are always at the end of the title, you can use substring_index. e.g.

select album, SUBSTRING_INDEX(SUBSTRING_INDEX(album, '(', -1),')',1) as year from myTable order by year desc;

[dataman911]

Thank you so much dopey!

It worked perfectly! 🙂