Sorting data which include integer and multi point

Question

In my project I store documents in MySQL DB. A table contains title number (t_nm) and title. I want to list this table with correct order by title number. Because of the data stored in varchar " SELECT * FROM table ORDER by t_nm " does not work properly.

What is the correct query?

t_nm (varchar) title (varchar)

+------+----------------------+
| t_nm | title                |
+------+----------------------+
| 0.1   | A.MANIVANNAN        |
| 1.0   | M.ELUMALAI          |
| 10.3  | SAMPATH.R           |
| 1.2   | S.PAULRAJ           |
| 1.2.1 | A.ASHOK KUMAR       |
| 2.1   | S.JAYAKUMAR         |
| 9.0   | S.CINRAS            |
| 10.1  | P.MURUGAN           |

I want to sort these data with the correct order like this

+------+----------------------+
| t_nm | title                |
+------+----------------------+
| 0.1   | A.MANIVANNAN        |
| 1.0   | M.ELUMALAI          |
| 1.2   | S.PAULRAJ           |
| 1.2.1 | A.ASHOK KUMAR       |
| 2.1   | S.JAYAKUMAR         |
| 9.0   | S.CINRAS            |
| 10.1  | P.MURUGAN           |
| 10.3  | SAMPATH.R           |

Answer 1

I don't have mySQL installed so I cannot test. Test casting the varchar into decimal before sorting.

it should go as follow : SELECT * FROM table ORDER by CAST(t_nmAS DECIMAL(10,6));

Update #1

Ok then explode it in an array with '." as the delemiter as integer

ex : SELECT * FROM table ORDER BY string_to_array(t_nmAS , '.')::int[] ASC