1
2.1
2.1.2
5.1
10
10.1
20
The default sort order just install the format of the string
1
10
10.1
20
2.1
2.1.2
5.1
Hope to be able to according to the size of the Numbers for sorting, I don't know is there a simple way
CodePudding user response:
SQL>
SQL> Create table test (id varchar (30));
The Table created
SQL> The begin
2 insert into the test values (' 1 ');
3 the insert into the test values (' 2.1 ');
4 the insert into the test values (' 2.1.2);
5 the insert into the test values (' 2.1.1);
6 insert into the test values (' 5.1 ');
7 the insert into the test values (' 10 ');
8 the insert into the test values (' 10.1 ');
9 the insert into the test values (' 20 ');
10 the end;
11/
PL/SQL procedure successfully completed
SQL> Select id
2 the from the test t
3 order by
4 0 + NVL (regexp_substr (id, '[^.] +, 1, 1), 0),
5 0 + NVL (regexp_substr (id, '[^.] +, 1, 2), 0),
6 0 + NVL (regexp_substr (id, '[^.] +, 1, 3), 0),
7 0 + NVL (regexp_substr (id, '[^.] +, 1, 4), 0)
8;
ID
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1
2.1
2.1.1
2.1.2
5.1
10
10.1
20
8 rows selected
SQL> Drop table test purge;
Table dropped
SQL>
CodePudding user response:
Conversion into fonts for sortingCodePudding user response:
Borrow a moderator dataSQL> Select t.i d from the test t order by to_number (regexp_replace (t.i d, '\.', ', 1, 2));
ID
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1
2.1
2.1.1
2.1.2
5.1
10
10.1
20
CodePudding user response:
Thank you for your answer, way of thinking is the interception and in front of the decimal point adding 0 converted to digital and then in the sortingCodePudding user response: