CI如何oder_by仅使用数据的最后4个字符

Question

+-----+----------+
| PID |   Number |
+-----+----------+
|   1 | 1.3.0001 |
|   2 | 1.3.0002 |
|   3 | 1.1.0003 |
|   4 | 1.2.0004 |
+-----+----------+

I have the table above, now how do I run the query order_by just the last 4 digit of the number ? If I run the query above with order by Number DESC, the result will be 1.3.0001, 1.3.0002, 1.2.0004, 1.1.0003, I want it to be ordered by just the last 4 digits so it should be 1.2.0004, 1.1.0003, 1.3.0002, 1.3.0001

PS: For a lot of reasons I cannot use order by PID or any other columns, I need to specifically order by column Number.

Thank you in advance

Answer 1

I think this should work, cant test it as i dont have your data

ORDER BY SUBSTRING_INDEX(number, '.', 2);

Or as @nogad has has said you can simplyfy this to order by a substring and include start position as the second parameter

ORDER BY SUBSTRING(number, 4)