I've problem to order the field sID_contents_p set to VARCHAR (255) form the table ttt stored an a database MySql version 8.0.17
This is the result on db-fiddle.com, which offers MySQL 8
I need this return, for example when sID_contents_p contains 1
+----------------+-----+
| sID_contents_p | sID |
+----------------+-----+
| 1.1 | 1 |
| 1.2 | 2 |
| 1.3 | 3 |
| 1.4 | 4 |
| 1.5 | 5 |
| 1.6 | 6 |
| 1.7 | 7 |
| 1.8 | 8 |
| 1.9 | 9 |
| 1.10 | 10 |
| 1.11 | 11 |
| 1.12 | 12 |
| 1.13 | 13 |
| 1.14 | 14 |
| 1.15 | 15 |
| 1.16 | 16 |
| 1.17 | 17 |
| 1.18 | 18 |
| 1.19 | 19 |
| 1.20 | 89 |
+----------------+-----+
I've tried this query but the return not what you want (see above)....
mysql> SELECT
sID_contents_p,
sID
FROM
`ttt` WHERE SUBSTRING_INDEX(sID_contents_p,".",1) = 1
ORDER BY
sID_contents_p ASC;
+----------------+-----+
| sID_contents_p | sID |
+----------------+-----+
| 1.1 | 1 |
| 1.10 | 10 |
| 1.11 | 11 |
| 1.12 | 12 |
| 1.13 | 13 |
| 1.14 | 14 |
| 1.15 | 15 |
| 1.16 | 16 |
| 1.17 | 17 |
| 1.18 | 18 |
| 1.19 | 19 |
| 1.2 | 2 |
| 1.20 | 89 |
| 1.3 | 3 |
| 1.4 | 4 |
| 1.5 | 5 |
| 1.6 | 6 |
| 1.7 | 7 |
| 1.8 | 8 |
| 1.9 | 9 |
+----------------+-----+
20 rows in set (0.03 sec)
The issue is the ordering. You have a string. One method is to order by the length first:
ORDER BY LENGTH(sID_contents_p), sID_contents_p
EDIT:
If the first component can also vary in length, then split the values and convert to ints:
ORDER BY SUBSTRING_INDEX(sID_contents_p, '.', 1) + 0,
SUBSTRING_INDEX(sID_contents_p, '.', -1) + 0