Oracle 10g SQL排序VARCHAR2

Question

Oracle 10g SQL排序VARCHAR2

6

我在使用Oracle 10g时遇到了排序问题，不确定它是否只存在于10g版本。

我有以下表：

ID  NAME
 1  A.1
 2  A.3
 3  A.4
 4  A.5
 5  A.2
 6  A.5.1
 7  A.5.2
 8  A.5.10
 9  A.5.10.1
10  A.5.3

执行通用的 SELECT NAME FROM table_name ORDER BY 1 会产生以下结果：

A.1
A.2
A.3
A.4
A.5
A.5.1
A.5.10
A.5.10.1
A.5.2
A.5.3

我希望在那些部分有大于9的数字时，它能正确排序，就像这样：

A.1
A.2
A.3
A.4
A.5
A.5.1
A.5.2
A.5.3
A.5.10
A.5.10.1

我有比这个更多的数字条目，长度不同，并且许多部分的数字段大于10。我试图在order by子句中尝试使用regexp_replace()，但没有成功。非常感谢您的帮助。

- Gabe Ortiz

5个回答

1

以下内容可能会给您一些启示。要按照“A.”的形式排序值，您可以按表达式的长度和表达式进行排序。因此，A.1和A.2在A.10之前，因为它们的长度较短。

您可以通过以下方式扩展此功能，并进行排序：

order by substr(val, 1, instr('.')),
         len(substr(val, 1, instr('.', 1, 2)),
         substr(val, 1, instr('.', 1, 2)),
         len(substr(val, 1, instr('.', 1, 3)),
         substr(val, 1, instr('.', 1, 3)) . . .

- Gordon Linoff

我不确定我是否理解代码逻辑。是缺少括号还是第一个len语句中的后续substr，len，substr？ - Gabe Ortiz

这是一个相当不错的解决方案，但它只处理有限数量的点。 - Luke101

@Luke101... ". . ." 的目的是指示可以使用与其他行相同的结构扩展代码。 - Gordon Linoff

0

这是一种方法。我不是说这是唯一的或者最好的方法，但它是A种方法：

SELECT ID,
       NAME
FROM
  (SELECT ID, NAME,
       INSTR(NAME, '.', 1, 1) AS FIRST_DOT_INDEX,
       INSTR(NAME, '.', 1, 2) AS SECOND_DOT_INDEX,
       INSTR(NAME, '.', 1, 3) AS THIRD_DOT_INDEX,
       INSTR(NAME, '.', 1, 4) AS FOURTH_DOT_INDEX
     FROM test_table)
ORDER BY SUBSTR(NAME, 1, FIRST_DOT_INDEX-1),
         TO_NUMBER(SUBSTR(NAME, FIRST_DOT_INDEX+1, (CASE WHEN SECOND_DOT_INDEX>0
                                                      THEN SECOND_DOT_INDEX-1
                                                      ELSE LENGTH(NAME)
                                                    END - FIRST_DOT_INDEX))),
         TO_NUMBER(CASE WHEN SECOND_DOT_INDEX = 0 AND THIRD_DOT_INDEX = 0
                     THEN '0'
                     ELSE SUBSTR(NAME, SECOND_DOT_INDEX+1, (CASE WHEN THIRD_DOT_INDEX>0
                                                              THEN THIRD_DOT_INDEX-1
                                                              ELSE LENGTH(NAME)
                                                            END - SECOND_DOT_INDEX)) 
                   END),
         TO_NUMBER(CASE WHEN THIRD_DOT_INDEX > 0
                     THEN SUBSTR(NAME, THIRD_DOT_INDEX+1, LENGTH(NAME) - THIRD_DOT_INDEX)
                     ELSE '0'
                   END);

分享并享受。

- Bob Jarvis - Слава Україні

这会考虑到我有不同长度的字符和点节吗？ - Gabe Ortiz

0

使用正则表达式可以解决您的问题，

select *
from new_table
  order by to_number(regexp_replace(name,'[[:alpha:].]*'));

这个查询的意思是我将列名中的字母和'.'字符替换为数字，然后进行排序。

希望这对你有所帮助，享受吧！

- planben

这个解决方案不会将A.5.11排在A.5.10.1之前吗？ - Luke101

那样做可以实现，但是首先按字符串长度排序，然后再按正确的顺序排序。 - Gabe Ortiz

0

我的问题实际上在另一篇我为类似但不相关的问题发布的帖子中得到了回答。

Oracle SQL doesn't support lookaround assertions, which would be useful for this case:

s/([0-9](?<![0-9]))/0\1/g

You'll have to use at least two replacements:

REGEXP_REPLACE(REGEXP_REPLACE(col, '([0-9]+)', '0\1'), '0([0-9]{2})', '\1')`

感谢 acheong87 提供的解决方案。 Oracle SQL Regexp_replace 匹配

- Gabe Ortiz

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- A.B.Cade · Accepted Answer

试试这个

WITH t AS
(
  SELECT id,name,
  xmltype('<r><c>' ||replace(NAME, '.', '</c><c>')||'</c></r>') AS xmlname
  FROM table1
)

SELECT name ,id
FROM t
ORDER BY lpad(extract(xmlname,'//c[1]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[2]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[3]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[4]/text()').getstringval(), 5, '0')

这是一个小提琴，在这里。