Now has a field sort, type is VARCHAR2, the inside of the content is 1.1 1.2.,,,
1.10 1.11The order by the sort of asc and order by cast (sort as float) asc is
1.1 1.10 1.11 1.2, 1.3
How to rewrite this sort?

CodePudding user response:

What do you want to change? In a timely manner by the numeric types looking at is also true for sorting

CodePudding user response:

The best solution is to add a field on this table, store order, or demand changes in the late will make SQL writing is very difficult to understand,

CodePudding user response:

Want to 3.1 3.2... Sorting instead of 3.1 3.10 3.11 3.10 this... 3.2 3.3

CodePudding user response:

The order by substr (sort, 0, instr (sort, '. ')) asc, to_number (substr (sort, instr (sort, '. ') + 1))