How do I extract the 1st position of a character string that are numbers and then order by number using MySQL

This can be accomplished using one select statement. It involves using the REPLACE, LEFT and CAST functions.

Say we have this string:

1. This is no. 1
9. This is no. 9
10. This is no. 10

If you use an “order by” you will get this:

1. This is no. 1
10. This is no. 10
9. This is no. 9

As it’s a character string, but we want to see it in numerical order.

SELECT * FROM `<table>` ORDER BY CAST(REPLACE(LEFT(<column>,2),'.','') AS SIGNED)

Now you will get the data back in the correct order.