Failed attempts at natural sort in MySQL

Question

I'm trying to achieve natural/alphanumeric sorting in MySQL and having no luck. I have read several Stack articles and various articles on the topic I found in google searches. Here is the basic query I began with:

select VideoTitle, VideoID
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY VideoTitle

That query yields this result set:

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 10 Pier Pressure
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 9 Storming the Castle

Notice this row is out of order from a natural sorting perspective:

Arrested Development Season 1: Ep. 10 Pier Pressure

Based on the posts/articles I read, I have also tried these approaches, with their accompanying result sets:

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY CAST(VideoTitle AS UNSIGNED), VideoTitle

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 10 Pier Pressure
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 9 Storming the Castle

---

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY LENGTH(VideoTitle), VideoTitle

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 10 Pier Pressure
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 9 Storming the Castle

---

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY VideoTitle + 0 ASC

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 9 Storming the Castle
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 10 Pier Pressure

Any ideas?

Answer 1

The best solution would be to store the show title, the season, the episode number, and the episode title in separate columns.

In this case, you might be able to do some string manipulation to get what you need.

ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(VideoTitle, ': ', -1), ' ', 2), ' ', -1)+0 

This is such a fragile solution, though.

Answer 2

If you really want, you can sort this by piece:

order by substring_index(VideoTitle, ' Season ', 1),
         substring_index(VideoTitle, ' Season ', -1) + 0,
         substring_index(VideoTitle, ': Ep. ', -1) + 0

This assumes that the string ' Season ' an : Ep. only occur once in each title.

The following version should also be pretty close, assuming seasons have only one digit:

order by substring_index(VideoTitle, ': Ep. ', 1),
         substring_index(VideoTitle, ': Ep. ', -1) + 0

Answer 3

If creating new columns for making your model better searchable I would go with a more elegant solution than having to break down the string by pieces.

select VideoTitle 
  from (select VideoTitle, 
               VideoTitle REGEXP '.+Ep\\. [0-9]{2}.+' vd2dig 
          from videos
       ) sub
 order by vd2dig, VideoTitle; 

This will essentially make VideoTitle when having two digits to 1 and when it has one digit it will be 0 so I order by this column first then the natural order on VideoTitle

See it working here: http://sqlfiddle.com/#!9/6abde/1