oracle 10g 在前面包含数字时对 varchar 列进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5257867/
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
10g ordering varchar columns when containing numbers in front
提问by simonC
I have an Oracle 10g DB and have a VARCHAR2 (2000 Character) column lets name it TEST which can contain numbers in front for example:
我有一个 Oracle 10g DB 并且有一个 VARCHAR2（2000 个字符）列，让它命名为 TEST，它可以在前面包含数字，例如：
test
1test
3test
When I call "... order by TEST asc" or simply "... order by TEST"
当我调用“... order by TEST asc”或简称为“... order by TEST”时
I get the results ordered like
我得到的结果如下
test
1test
3test
But I would like to get the results ordered like this:
但我想得到这样排序的结果：
1test
3test
test
So the numbered inserts first, is there a method to achieve this?
所以先编号插入，有没有办法实现这一点？
采纳答案by Alex Poole
What is your NLS_SORTset to? (select sys_context('USERENV', 'NLS_SORT') from dual). If it is BINARYthen the sort order is based on the numeric value of each character, so it's dependant on the database character set. If it's something else then you might want to override it.
您的NLS_SORT设置为什么？( select sys_context('USERENV', 'NLS_SORT') from dual). 如果是，BINARY则排序顺序基于每个字符的数值，因此它取决于数据库字符集。如果它是别的东西，那么你可能想要覆盖它。
You can change the sort order at database or session level by modifying that parameter, but you can also change it for a single query:
您可以通过修改该参数来更改数据库或会话级别的排序顺序，但您也可以为单个查询更改它：
order by nlssort(test,'NLS_SORT=BINARY')
Depending on your character set you might need to experiment with different values instead of BINARY. You can get a list of all the valid values with select value from v$nls_valid_values where parameter = 'SORT'. But note the potential performance impacted mentioned in the NLS_SORT documentation.
根据您的字符集，您可能需要尝试不同的值而不是BINARY. 您可以使用 获取所有有效值的列表select value from v$nls_valid_values where parameter = 'SORT'。但请注意 NLS_SORT 文档中提到的潜在性能影响。
The nlssort()function is documented here.
该nlssort()函数记录在此处。
回答by Greg Reynolds
You could replace the previous answers substr test with a regexp
您可以用正则表达式替换以前的答案 substr test
order by case when regexp_instr(test,'[0-9]+') = 1 then
to_number(regexp_substr(test,'[0-9]+'))
else
null
end nulls last, test
It should be something like that - you can tweak the regexp according to what you want.
它应该是这样的 - 您可以根据需要调整正则表达式。
回答by Tony Andrews
One way:
单程：
order by case when substr(test,1,1) between '0' and '9' then 1 else 2 end,
test
回答by user2862073
Using substr is working fine.If we want numbers in ascending order and character in descending order.what is the workaround.
使用 substr 工作正常。如果我们希望数字按升序排列，字符按降序排列。解决方法是什么。
