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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

sixty three − = sixty one