Oracle - 像 NUMBER 一样对 VARCHAR2 字段进行排序 - 我找到了一个解决方案，需要对此进行解释
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4953333/
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
Oracle - Sorting a VARCHAR2 field like a NUMBER - I found a solution, need explanation on it
提问by contactmatt
I have a VARCHAR2 column that I want to sort numerically. 99% (or possibly even 100%) of the time it will contain numbers. I was looking around and found this solution. Quoting the source:
我有一个 VARCHAR2 列，我想对其进行数字排序。99%（甚至可能 100%）的时间它会包含数字。我环顾四周，找到了这个解决方案。引用来源：
Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.
For example,
select * from supplier order by lpad(supplier_id, 10);
This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.
请记住，我们的目标是按升序（基于其数值）对 supply_id 字段进行排序。为此，请尝试使用 LPAD 函数。
例如，
从供应商订单中选择 * lpad(supplier_id, 10);
此 SQL 用最多 10 个字符的空格填充供应商 ID 字段的前面。现在，您的结果应该按数字升序排列。
I've played around a little bit with this solution and it seems to be workign (so far), but howdoes it work, can anyone explain?
我已经玩了一点这个解决方案，它似乎是有效的（到目前为止），但是它是如何工作的，谁能解释一下？
回答by Adriaan Stander
When sorting strings/varchar, the field is always serted from left to right, like you would sort normal words.
在对字符串/varchar 进行排序时，该字段总是从左到右插入，就像对普通单词进行排序一样。
That is why you have problems when sorting
这就是你在排序时遇到问题的原因
1
2
3
10
11
20
which would be sorted as
这将被排序为
1
10
11
2
20
3
But, now if you pad the values left, you will have something like
但是，现在如果你填充剩下的值，你会得到类似的东西
001
002
003
010
011
020
which would sort correctly
这将正确排序
