SQL 如何在不同情况下按表列排序（Oracle）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9788838/
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
How can I sort by a table column in varying cases (Oracle)
提问by acidRain
How can I sort a table with a column of varchar2 with characters in varying cases (UPPERand lower)?
如何在不同情况下（UPPER和lower）对包含 varchar2 列的表格进行排序？
For example, when I do an order by of the Name column, I get the following results:
例如，当我对 Name 列执行 order by 时，我得到以下结果：
ANNIE
BOB
Daniel
annie
bob
What I want is something like this:
我想要的是这样的：
ANNIE
annie
BOB
bob
Daniel
回答by Michael Durrant
Use lower(field), e.g.
使用lower(field)，例如
select * from tbl order by lower(name)
If you need to address special characters for non-english languages then the other answers about NLSSORT may be what you need. If you don't I would try and KISS and use lower()as it is very easy to remember and use and be read by others (maintainability).
如果您需要解决非英语语言的特殊字符，那么有关 NLSSORT 的其他答案可能就是您所需要的。如果你不这样做，我会尝试和 KISS 并使用lower()它，因为它很容易记住和使用并被其他人阅读（可维护性）。
回答by Shannon Severance
Another option is the use of the NLSSORTfunction to perform linguistic sorting:
SQL> with test as (select 'ANNIE' as col from dual
2 union all select 'BOB' from dual
3 union all select 'Daniel' from dual
4 union all select 'annie' from dual
5 union all select 'bob' from dual
6 union all select '?ngstr?m' from dual
7 union all select '?ngstr?m' from dual)
8 select col
9 from test
10 order by nlssort(col, 'NLS_SORT = WEST_EUROPEAN')
11 /
COL
----------
?ngstr?m
?ngstr?m
ANNIE
annie
BOB
bob
Daniel
The advantages are more flexibility. One can sort characters with accents as well as different cases together. One can choose to treat some characters in a language specific way by specifying different values for NLS_SORT. Defines an order within the set of equivalent characters. So 'A' and 'a' are sorted together, but within the 'a's, the upper case comes first. Disadvantages I expect that NLSSORT uses more CPU than LOWER, though I have not bench marked it. And NLSSORT will only use a prefix of longer strings:
优点是更灵活。可以将带有重音符号和不同大小写的字符排序在一起。通过为 NLS_SORT 指定不同的值，可以选择以特定于语言的方式处理某些字符。定义等效字符集中的顺序。所以 'A' 和 'a' 被排序在一起，但在 'a's 中，大写字母在前。缺点 我预计 NLSSORT 使用的 CPU 比 LOWER 多，尽管我没有对其进行基准测试。并且 NLSSORT 将只使用更长字符串的前缀：
The string returned, also known as the collation key, is of RAW data type. The length of the collation key resulting from a given char value for a given collation may exceed 2000 bytes, which is the maximum length of the RAW value returned by NLSSORT. In this case, NLSSORT calculates the collation key for a maximum prefix, or initial substring, of char so that the calculated result does not exceed 2000 bytes. For monolingual collations, for example FRENCH, the prefix length is typically 1000 characters. For multilingual collations, for example GENERIC_M, the prefix is typically 500 characters. The exact length may be lower or higher depending on the collation and the characters contained in char.
返回的字符串，也称为整理键，是 RAW 数据类型。由给定排序规则的给定字符值产生的排序规则键的长度可能超过 2000 字节，这是 NLSSORT 返回的 RAW 值的最大长度。在这种情况下，NLSSORT 计算 char 的最大前缀或初始子字符串的排序规则键，以便计算结果不超过 2000 个字节。对于单语排序规则，例如 FRENCH，前缀长度通常为 1000 个字符。对于多语言归类，例如 GENERIC_M，前缀通常为 500 个字符。确切的长度可能更低或更高，具体取决于排序规则和 char 中包含的字符。
回答by Mark J. Bobak
If you're on relatively recent versions of Oracle, you should look at setting NLS_SORT/NLS_COMP, rather than using the LOWER() function.
如果您使用的是相对较新的 Oracle 版本，您应该考虑设置 NLS_SORT/NLS_COMP，而不是使用 LOWER() 函数。
If you don't want to globally affect the instance, you can use the NLSSORT() function to set the NLS_SORT for the scope of a specific query.
如果不想全局影响实例，可以使用 NLSSORT() 函数为特定查询的范围设置 NLS_SORT。
SQL> create table case_insensitive(a varchar2(10));
Table created.
SQL> insert into case_insensitive values('D');
1 row created.
SQL>
SQL>
SQL> c/'D/'c
1* insert into case_insensitive values('c')
SQL> /
1 row created.
SQL> c/'c/'B
1* insert into case_insensitive values('B')
SQL> /
1 row created.
SQL> c/'B/'a
1* insert into case_insensitive values('a')
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from case_insensitive;
A
----------
D
c
B
a
SQL> select * from case_insensitive order by a;
A
----------
B
D
a
c
SQL> select * from case_insensitive order by nlssort(a,'NLS_SORT=BINARY_CI');
A
----------
a
B
c
D
A good example of this can be found here.
回答by knightz
You can use INITCAPe.g.
你可以使用INITCAP例如
SELECT fld FROM tbl ORDER BY INITCAP(fld) ASC;
回答by A A Nayak
You can use the Order by cluse for this
您可以为此使用 Order by cluse
select col_name from table_name
order by col_name ;
