SQL Oracle 排序字符串（数字）和（带数字的字母）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15572737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Oracle Sort string (numbers) and (letters with numbers)
提问by user2199531
I am new to oracle and I have a problem. I have a column named file_id.
我是 oracle 的新手，我遇到了问题。我有一个名为 file_id 的列。
When I do an order by it sorts strings such as
当我通过它排序字符串时，例如
1
1
10
100
11
11
110
114
12
300
31
4200
B14
B170
B18
edit: I would like it to sort this way.
编辑：我希望它以这种方式排序。
1
1
10
11
11
12
31
100
300
4200
B14
B18
B170
The answer below works perfectly. Only other problem I ran into now..I have records that are blank. How could I make the blank records order at the end?
下面的答案完美无缺。我现在遇到的唯一其他问题..我的记录是空白的。我怎么能在最后制作空白记录顺序？
1
1
10
11
11
12
31
100
300
4200
BLANK
BLANK
BLANK
BLANK
BLANK
B14
B18
B170
Thank you for your help.
感谢您的帮助。
回答by Egor Skriptunoff
回答by Northmoor
This is an old question, but it was the first hit on google so I thought I'd share an alternative solution:
这是一个老问题，但它是谷歌上的第一个热门问题，所以我想我会分享一个替代解决方案：
select column
from table
order by
LPAD(column, 10)
The LPAD function pads the left-side of the string with spaces so that the results will be sorted numerically. This works for non-numeric values, and null values will be sorted last. This works well if you know the maximum length of the strings to be sorted (you may need to adjust the second parameter to suit your needs).
LPAD 函数用空格填充字符串的左侧，以便结果将按数字排序。这适用于非数字值，空值将排在最后。如果您知道要排序的字符串的最大长度（您可能需要调整第二个参数以满足您的需要），这很有效。
Source: http://www.techonthenet.com/oracle/questions/sort1.php
来源：http: //www.techonthenet.com/oracle/questions/sort1.php
EDIT:
I noticed that while my solution works well for my case, the output is slightly different from the accepted answer (http://www.sqlfiddle.com/#!4/d935b8/2/0):
编辑：
我注意到虽然我的解决方案适用于我的情况，但输出与接受的答案略有不同（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 should come after 300. For my situation this is good enough, but this may not always be the case.
4200 应该在 300 之后。对于我的情况，这已经足够了，但情况可能并非总是如此。
回答by Francisco M
Based on the previous solution:
基于之前的解决方案：
SELECT column
FROM table
ORDER BY LPAD(column, (SELECT MAX(LENGTH(column)) FROM table)) ASC
The advantage of this approach is that you don't need know the table column size.
这种方法的优点是您不需要知道表列的大小。
