mysql order by of unpatterned string with numbers at the end

Question

For example I have table1 that has column named "Column1" and it has the ff values: item 1, item 2, item 10, old item 3.5, old item 20.1 when I use SELECT Column1 FROM table1 ORDER BY Column1 ASC the result is:

-----------------
|Column1        |
-----------------
| item 1        |
| item 10       |
| item 2        |
| old item 20.1 |
| old item 3.5  |

is there a way that I can make it like this?

-----------------
|Column1        |
-----------------
| item 1        |
| item 2        |
| item 10       |
| old item 3.5  |
| old item 20.1 |

I see the post from MySQL order by string with numbers but it only has a fixed pattern.

score 0 · Answer 1 · answered Jun 16 '15 at 13:36

The content of the column is sorted as varchar. i suggest you to divide the string into two columns. (C1 = Varchar // C2 = number) f.ex.: R1C1: item R1C2: 1 R2C1: item R2C2: 10 and so on.

After that you're able to order by C2.

Another solution is to split the content of C1 by a regular Expression and then ordering by the result of the regex.

mysql order by of unpatterned string with numbers at the end

1 Answers1