How to Sorting text that contains ordinal numbers?

I am working on an application where members can select a DVD and add it to their "wish list". A report can be开发者_如何学Python printed with the current wish list. If the DVD title contains an ordinal number (first, second, third, fourth, etc), my customer would like to sort by the number (1, 2, 3, 4, etc).

For example, if this script is used to create, populate, and query a table:

CREATE TABLE Dvd (
   TitleID  INT IDENTITY PRIMARY KEY
   ,Title  VARCHAR(256)
)

GO

INSERT INTO Dvd (Title) VALUES ('M*A*S*H - The Complete First Season')
INSERT INTO Dvd (Title) VALUES ('M*A*S*H - The Complete Second Season')
INSERT INTO Dvd (Title) VALUES ('M*A*S*H - The Complete Third Season')
INSERT INTO Dvd (Title) VALUES ('M*A*S*H - The Complete Fourth Season')

SELECT Title FROM Dvd ORDER BY Title

The query returns the following results:

M*A*S*H - The Complete First Season
M*A*S*H - The Complete Fourth Season
M*A*S*H - The Complete Second Season
M*A*S*H - The Complete Third Season

Does anybody have any suggestions for an efficient way to sort the titles so they would be listed in this order, based on the actual number associated with the ordinal number text:

M*A*S*H - The Complete First Season
M*A*S*H - The Complete Second Season
M*A*S*H - The Complete Third Season
M*A*S*H - The Complete Fourth Season

---

Assuming the TitleID's increase in order, just order by TitleID.