blocks|key|462535|text|如果级别数是固定的(例如，最大值)。4)你可以用这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|462536|ORDER+BY+
++++TO_NUMBER(REGEXP_SUBSTR(Sections,+'\d%2B',+1,+1))+NULLS+FIRST,+
++++TO_NUMBER(REGEXP_SUBSTR(Sections,+'\d%2B',+1,+2))+NULLS+FIRST,+
++++TO_NUMBER(REGEXP_SUBSTR(Sections,+'\d%2B',+1,+3))+NULLS+FIRST,+
++++TO_NUMBER(REGEXP_SUBSTR(Sections,+'\d%2B',+1,+4))+NULLS+FIRST|code-block|syntax|javascript|462537|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In case the number of level is fix (e.g. max. 4) you can use this one:

<pre><code>ORDER BY 
 TO_NUMBER(REGEXP_SUBSTR(Sections, '\d+', 1, 1)) NULLS FIRST, 
 TO_NUMBER(REGEXP_SUBSTR(Sections, '\d+', 1, 2)) NULLS FIRST, 
 TO_NUMBER(REGEXP_SUBSTR(Sections, '\d+', 1, 3)) NULLS FIRST, 
 TO_NUMBER(REGEXP_SUBSTR(Sections, '\d+', 1, 4)) NULLS FIRST
</code></pre>

blocks|key|462573|text|当已知最大深度时，可以将该区段拆分为子部分：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|462574|SQL>+SELECT+SECTION+FROM+DATA
++2+++ORDER+BY+to_number(regexp_substr(SECTION,+'[%5E.]%2B',+1,+1))+NULLS+FIRST,
++3++++++++++++to_number(regexp_substr(SECTION,+'[%5E.]%2B',+1,+2))+NULLS+FIRST,
++4++++++++++++to_number(regexp_substr(SECTION,+'[%5E.]%2B',+1,+3))+NULLS+FIRST;

SECTION
-------
1
1.1
1.1.2
1.1.6
6.1
6.2
[...]
8.5
10
11|code-block|syntax|javascript|462575|如果子部分的最大深度未知(但假定小于几百个8位字符数据库，或ANSI字符数据库中不足几千个)，则可以定义一个函数，将不可排序的数字转换为可排序字符：|462576|SQL>+CREATE+OR+REPLACE+FUNCTION+order_section+(p_section+VARCHAR2)
++2+++++RETURN+VARCHAR2+IS
++3+++++l_result+VARCHAR2(4000);
++4++BEGIN
++5+++++FOR+i+IN+1..regexp_count(p_section,+'[%5E.]%2B')+LOOP
++6++++++++l_result+:=+l_result
++7++++++++++++++++++++%7C%7C+CASE+WHEN+i+>+1+THEN+'.'+END
++8++++++++++++++++++++%7C%7C+CHR(64%2Bregexp_substr(p_section,+'[%5E.]%2B',+1,+i));
++9+++++END+LOOP;
+10+++++RETURN+l_result;
+11++END;
+12++/

Function+created

SQL>+SELECT+SECTION,+order_section(SECTION)
++2++++FROM+DATA
++3+++ORDER+BY+2;

SECTION+ORDER_SECTION(SECTION)
-------+-------------------------
1+++++++A
1.1+++++A.A
1.1.2+++A.A.B
1.1.6+++A.A.F
6.1+++++F.A
6.2+++++F.B
[...]
8.5+++++H.E
10++++++J
11++++++K|462577|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

When the maximum depth is known, you can split the section in sub-sections:

<pre><code>SQL&gt; SELECT SECTION FROM DATA
 2 ORDER BY to_number(regexp_substr(SECTION, '[^.]+', 1, 1)) NULLS FIRST,
 3 to_number(regexp_substr(SECTION, '[^.]+', 1, 2)) NULLS FIRST,
 4 to_number(regexp_substr(SECTION, '[^.]+', 1, 3)) NULLS FIRST;

SECTION
-------
1
1.1
1.1.2
1.1.6
6.1
6.2
[...]
8.5
10
11
</code></pre>

If the maximum depth of sub-sections is unknown (but presumably less than a couple hundred on 8-bit character databases or less than a few thousands in ANSI-character databases), you could define a function that converts your unsortable digits into sortable characters:

<pre><code>SQL&gt; CREATE OR REPLACE FUNCTION order_section (p_section VARCHAR2)
 2 RETURN VARCHAR2 IS
 3 l_result VARCHAR2(4000);
 4 BEGIN
 5 FOR i IN 1..regexp_count(p_section, '[^.]+') LOOP
 6 l_result := l_result
 7 || CASE WHEN i &gt; 1 THEN '.' END
 8 || CHR(64+regexp_substr(p_section, '[^.]+', 1, i));
 9 END LOOP;
 10 RETURN l_result;
 11 END;
 12 /

Function created

SQL&gt; SELECT SECTION, order_section(SECTION)
 2 FROM DATA
 3 ORDER BY 2;

SECTION ORDER_SECTION(SECTION)
------- -------------------------
1 A
1.1 A.A
1.1.2 A.A.B
1.1.6 A.A.F
6.1 F.A
6.2 F.B
[...]
8.5 H.E
10 J
11 K
</code></pre>

blocks|key|896883|text|下面是我在一般情况下得到的解决方案(在不知道点数的情况下)--保留第一个点，用零替换所有其他点，因此您将只有一个浮点数，您可以通过以下方式应用顺序：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|896884|SELECT+SECTIONS+FROM+T_TABLE
ORDER+BY+TO_NUMBER(SUBSTR(SECTIONS,0,DECODE(INSTR(SECTIONS,'.'),0,LENGTH(SECTIONS)%2B1,INSTR(SECTIONS,'.')))+%7C%7C
REPLACE(SUBSTR(SECTIONS,DECODE(INSTR(SECTIONS,'.'),0,LENGTH(SECTIONS)%2B1,INSTR(SECTIONS,'.'))),'.','0'))|code-block|syntax|javascript|896885|它可以用常规的注释来重写，但是我并不很熟悉它们，所以只使用基本的Oracle函数:)|896886|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is the solution I've ended up with for general case (when number of dots is not known) - leave the first dot as it is, and replace all others with zeroes, so you will have just a float number on which you can apply order by:

<pre><code>SELECT SECTIONS FROM T_TABLE
ORDER BY TO_NUMBER(SUBSTR(SECTIONS,0,DECODE(INSTR(SECTIONS,'.'),0,LENGTH(SECTIONS)+1,INSTR(SECTIONS,'.'))) ||
REPLACE(SUBSTR(SECTIONS,DECODE(INSTR(SECTIONS,'.'),0,LENGTH(SECTIONS)+1,INSTR(SECTIONS,'.'))),'.','0'))
</code></pre>

<hr>

It can be rewritten more ellegantly using regular expresions, but I'm not really familiar with them, so just used basic Oracle functions :)

blocks|key|895357|text|你可以试试-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|895358|最好的部分--不需要担心级别的深度|offset|length|style|BOLD|895359|SELECT
Section
FROM+SectionData
ORDER+BY
CAST+(CASE+WHEN+CHARINDEX('.',Section)+>+0
THEN+SUBSTRING(Section,0,CHARINDEX('.',Section))
ELSE+Section+END+AS+INT)
,REPLACE(Section,'0',':')|code-block|syntax|javascript|895360|是如何工作的：-|895361|因此，首先根据第一个DOT之前的整数进行排序。|blockquote|895362|由于您的节是字符串类型的，所以排序是基于ASCII代码进行的。另外，在你的章节中最重要的部分是第一组在第一个DOT之前的数字集。|895363|这是你的第二个排序标准。|895364|现在，它将产生所有的问题-所以用ASCII值高于9的任何值替换'0‘。|895365|我已经用一些基本的组合(包括更高的深度)来测试它--在使用它之前，继续测试它吧。|895366|entityMap^0|0|0|C|0|0|0|8|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@$D|13|E|14|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|15|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|16|8|@$D|17|E|18|F|G]]|9|@]|A|$]]|$1|O|3|P|5|Q|7|19|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1A|8|@]|9|@]|A|$]]|$1|T|3|U|5|Q|7|1B|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1C|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1D|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|10|$]]

You can try this out - 

Best part - Don't need to worry about the depth of levels

<pre><code>SELECT
Section
FROM SectionData
ORDER BY
CAST (CASE WHEN CHARINDEX('.',Section) &gt; 0
THEN SUBSTRING(Section,0,CHARINDEX('.',Section))
ELSE Section END AS INT)
,REPLACE(Section,'0',':')
</code></pre>

How it works:-

<blockquote>
 So sort first based on the integer before the first DOT.
</blockquote>

Since your Sections are of string type, the sorting is done based on ASCII codes. Also, the most significant part of your section is the first set of digist before the first DOT.

<blockquote>
 This is your second sorting criteria.
</blockquote>

Now, its the 0 which would create all the problems - so replace the '0' with anything which has ASCII value higher than that of 9.

I've tested it with some basic combinations (including higher depths) - go ahead and test it properly before using it.

blocks|key|2634050|text|我觉得最简单的..。复制并运行以查看输出：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2634051|SELECT+val+FROM++--,to_number(trim(BOTH+'.'+FROM+substr(val,+1,+2)))+num_val,
(
+SELECT+'1'+val+FROM+dual
+UNION+ALL
+SELECT+'7.1'+FROM+dual
+UNION+ALL
+SELECT+'6.2'+FROM+dual
+UNION+ALL
+SELECT+'7.1'+FROM+dual
+UNION+ALL
+SELECT+'7.4'+FROM+dual
+UNION+ALL
+SELECT+'6.8.3'+FROM+dual
+UNION+ALL
+SELECT+'6.8.2'+FROM+dual
+UNION+ALL
+SELECT+'10'+FROM+dual
+UNION+ALL
+SELECT+'1.1'+FROM+dual
+UNION+ALL
+SELECT+'7.6'+FROM+dual
+UNION+ALL
+SELECT+'6.1'+FROM+dual
+UNION+ALL
+SELECT+'11'+FROM+dual
+UNION+ALL
+SELECT+'8.3'+FROM+dual
+UNION+ALL
+SELECT+'8.5'+FROM+dual
+UNION+ALL
+SELECT+'1.1.2'+FROM+dual
+UNION+ALL
+SELECT+'6.4'+FROM+dual
+UNION+ALL
+SELECT+'6.6'+FROM+dual
+UNION+ALL
+SELECT+'8.4'+FROM+dual
+UNION+ALL
+SELECT+'1.1.6'+FROM+dual
+UNION+ALL
+SELECT+'6.8.1'+FROM+dual
+UNION+ALL
+SELECT+'7.7.1'+FROM+dual
+UNION+ALL
+SELECT+'7.5'+FROM+dual
+UNION+ALL
+SELECT+'7.3'+FROM+dual
)
ORDER+BY+to_number(trim(BOTH+'.'+FROM+substr(val,+1,+2)))|code-block|syntax|javascript|2634052|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Simplest I think... Copy and run to see the output:

<pre><code>SELECT val FROM --,to_number(trim(BOTH '.' FROM substr(val, 1, 2))) num_val,
(
 SELECT '1' val FROM dual
 UNION ALL
 SELECT '7.1' FROM dual
 UNION ALL
 SELECT '6.2' FROM dual
 UNION ALL
 SELECT '7.1' FROM dual
 UNION ALL
 SELECT '7.4' FROM dual
 UNION ALL
 SELECT '6.8.3' FROM dual
 UNION ALL
 SELECT '6.8.2' FROM dual
 UNION ALL
 SELECT '10' FROM dual
 UNION ALL
 SELECT '1.1' FROM dual
 UNION ALL
 SELECT '7.6' FROM dual
 UNION ALL
 SELECT '6.1' FROM dual
 UNION ALL
 SELECT '11' FROM dual
 UNION ALL
 SELECT '8.3' FROM dual
 UNION ALL
 SELECT '8.5' FROM dual
 UNION ALL
 SELECT '1.1.2' FROM dual
 UNION ALL
 SELECT '6.4' FROM dual
 UNION ALL
 SELECT '6.6' FROM dual
 UNION ALL
 SELECT '8.4' FROM dual
 UNION ALL
 SELECT '1.1.6' FROM dual
 UNION ALL
 SELECT '6.8.1' FROM dual
 UNION ALL
 SELECT '7.7.1' FROM dual
 UNION ALL
 SELECT '7.5' FROM dual
 UNION ALL
 SELECT '7.3' FROM dual
)
ORDER BY to_number(trim(BOTH '.' FROM substr(val, 1, 2)))
</code></pre>

<blockquote>
 UPDATE:
 
 ORACLE VERSION 10G
</blockquote>

I have a list of records in <code>Oracle</code> as follows, these are actually sections of various books 
The records are generated in the below format

[main topic].[sub topic].[first level section] ..... .[last level section]

<pre><code>Sections
--------
1
7.1
6.2 
7.1
7.4
6.8.3
6.8.2
10
1.1
7.6
6.1
11
8.3
8.5
1.1.2
6.4
6.6
8.4
1.1.6
6.8.1
7.7.1
7.5
7.3
</code></pre>

I want to order this like as follows

<pre><code> 1
 1.1
 1.1.2
 1.1.6
 6.2 
 6.4 
 6.5 
 6.6 
 6.7 
 6.8.1 
 6.8.2 
 6.8.3 
 7.2 
 7.3 
 7.4 
 7.5 
 7.6 
 7.7.1 
 7.7.2 
 8.3 
 8.4 
 8.5
 10
</code></pre>

But as the field is not a <code>numeric</code> <code>datatype</code> the sorting results in something like this

<pre><code>1
10
1.1
1.1.2
1.1.6
....
.....
8.5
</code></pre>

How can I sort them. I am unable to convert them to number due to multiple number of decimal points.

Is there any function in <code>oracle</code> that supports such a sorting technique

Sorting records from Oracle with multiple decimal points (.)

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

更新：甲骨文10G版我在Oracle中有一个记录列表，如下所示，这些实际上是各种书籍的部分这些记录以以下格式生成主topic.sub topic.first电平段..last级区段Sections--------17.16.2 7.17.46.8.36.8.2101.17.66.1118.38.51.1.26.46.6...

问用多个小数点对Oracle中的记录进行排序(.)
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用多个小数点对Oracle中的记录进行排序(.)EN