SQL Oracle对字符串（数字）和（字母与数字）进行排序

Question

我是Oracle的新手，遇到了一个问题。我有一个名为file_id的列。

当我使用order by时，它会对字符串进行排序，例如：

1
1 
10 
100 
11 
11
110 
114
12
300 
31
4200
B14
B170
B18

编辑：我希望它能以这种方式排序。

1
1
10
11
11
12
31
100
300
4200
B14
B18 
B170

下面的回答完全有效。我遇到的唯一另一个问题是，我有一些空记录。如何使这些空记录在最后排序？

1 
1 
10 
11 
11 
12 
31 
100 
300 
4200 
BLANK 
BLANK 
BLANK 
BLANK 
BLANK 
B14 
B18 
B170

谢谢你的帮助。

- user2199531

---

“Properly” 是什么意思；您想要数字排序吗？接着是二进制排序？ - Ben

我已经编辑过了，展示了我想要进行排序的方式。非常感谢您的快速回复。 - user2199531

@user2199531 在order by的开头添加一个case语句.. order by case when col is null then 2 else 1 end, regexp_substr(... - DazzaL

@user2199531，请在order by中添加"NULLS LAST"以将null值排在最后。 - Ronnis

Answer 1

select column 
from table
order by 
  regexp_substr(column, '^\D*') nulls first,
  to_number(regexp_substr(column, '\d+'))

fiddle

- Egor Skriptunoff

---

喜欢这个。我将它与替换结合使用，以去除标点符号并规范化大小写以进行通用自然排序。 regexp_substr(REGEXP_REPLACE(TRIM(LOWER(coulmn)), '[[:punct:]]', ''), '^\D*') nulls first, to_number(regexp_substr(REGEXP_REPLACE(TRIM(LOWER(column)), '[[:punct:]]', ''), '\d+')) - Gary Stanton

@aashii - 将\d+替换为[0-9.,]+。在将它们转换为数字之前，您可能还需要将点号和逗号进行替换（或反转）。 - Egor Skriptunoff

它具有表达式和排序选项（例如升序/降序，空值优先/最后） - Egor Skriptunoff

如果我还有一个值B，我该如何让B出现在B14之前？ - anishjp

@anishjp - 在结尾添加 nulls first，请参见 fiddle。 - Egor Skriptunoff

Answer 2

这是一个老问题，但它在谷歌上是第一个搜索结果，所以我想分享另一种解决方案:

select column
from table
order by 
  LPAD(column, 10)

LPAD函数将字符串左侧填充空格，以使结果以数字方式排序。这适用于非数字值，而null值将被排在最后。如果您知道要排序的字符串的最大长度（您可能需要调整第二个参数以满足您的需求），则此方法效果良好。

来源：http://www.techonthenet.com/oracle/questions/sort1.php 编辑：我注意到虽然我的解决方案对我的情况很有效，但输出与接受的答案略有不同(http://www.sqlfiddle.com/#!4/d935b8/2/0):

1
1
10
11
11
12
31
100
110
114
300
A14
A18
4200
A170
(null)
(null)

4200 应该在 300 后面。对于我的情况来说这已经足够好了，但并不总是如此。

- Northmoor

Answer 3

基于之前的解决方案:

SELECT column
FROM table
ORDER BY LPAD(column, (SELECT MAX(LENGTH(column)) FROM table)) ASC

这种方法的优点在于您不需要知道表列的大小。

- Francisco M