How to sort movie names (varchar) that contain sequel numbers correctly? [duplicate]

This question already has answers here: 开发者_StackOverflow中文版 Natural Sort in MySQL (22 answers) Closed 5 years ago.

Let's say I have a table of movies. The name of the movie is a varchar. But sometimes the movie names contain sequel (or series or episode) numbers that go over 9. How can I sort the movie name to handle numbers like a human would?

This is how it sorts by default:

select * from movies order by name

Alpha
Beta
Gamma 1
Gamma 10
Gamma 2
Gamma 3
Gamma 4
Gamma 5
Gamma 6
Gamma 7
Gamma 8
Gamma 9
Delta

See the 10 after 1? I know why it does that, what I want to know is how to make it sort like this:

Alpha
Beta
Gamma 1
Gamma 2
Gamma 3
Gamma 4
Gamma 5
Gamma 6
Gamma 7
Gamma 8
Gamma 9
Gamma 10
Delta

I have looked for solutions all over and haven't found anything that solves this.

Please note that this is just a simple example, the actual names can be much more complex, like Gamma The Explorer 1. episode or Gamma The Explorer 2. series 1. episode or Gamma The Explorer 3. episode Director's Cut and that most other movie names don't have numbers in them, so please no solutions that count on the number being in a specific place or position in the string or sorting using name+0 - that's not the solution I want.

I know for a fact that Windows Explorer, among other software, handles sorting file names in the correct (human) fashion, and I don't believe MySQL wouldn't be able to handle something so obvious, so there ought to be a feasible solution for this.

Edit: Apparently it's called natural sorting. Does MySQL really not have this at all? Optionally: What is UDF, and where do I get one for natural sorting, and how do I get it on my server?

---

  SELECT movies.*, 
         SUBSTRING_INDEX(name, ' ', 1) AS name_str,
         SUBSTRING_INDEX(name, ' ', -1) + 0 AS name_num
    FROM movies
ORDER BY name_str, name_num