I have a table of posts with titles that are in "human" alphabetical order but not in computer alphabetical order. These are in two flavors, numerical and alphabetical:

• Numerical: Figure 1.9, Figure 1.10, Figure 1.11...

• Alphabetical: Figure 1A ... Figure 1Z ... Figure 1AA

If I orderby title, the result is that 1.10-1.19 come between 1.1 and 1.2, and 1AA-1AZ come between 1A and 1B. But this is not what I want; I want "human" alphabetical order, in which 1.10 comes after 1.9 and 1AA comes after 1Z.

I am wondering if there's still a way in SQL to get the order that I want using string manipulation (or something else I haven't thought of).

I am not an expert in SQL, so I don't know if this is possible, but if there were a way to do conditional replacement, then it seems I could impose the order I want by doing this:

1. delete the period (which can be done with replace, right?)

2. if the remaining figure number is more than three characters, add a 0 (zero) after the first character.

This would seem to give me the outcome I want: 1.9 would become 109, which comes before 110; 1Z would become 10Z, which comes before 1AA. But can it be done in SQL? If so, what would the syntax be?

Note that I don't want to modify the data itself—just to output the results of the query in the order described.

This is in the context of a Wordpress installation, but I think the question is more suitably an SQL question because various things (such as pagination) depend on the ordering happening at the MySQL query stage, rather than in PHP.