>我的数据库中有这样的列数据
data
-----
1
2
A
3
4
B
我有以下nls设置
SELECT * From NLS_SESSION_PARAMETERS;
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_COMP BINARY
当我查询数据库以获取**select data from <mytable> order by data **
时,我得到的结果如下
data
-----
1
2
3
4
A
B
通过休眠的相同查询是先给出字母结果,然后给出数字的结果
data
-----
A
B
1
2
3
4
但是,我希望列首先显示数字,然后通过休眠显示字母
data
------
1
2
3
4
A
B
有人可以帮我解决这个问题吗?
您可以使用正则表达式以这种方式进行排序,
select data from <mytable> order by REGEXP_REPLACE(data ,'[^0-9]'), REGEXP_REPLACE(data ,'[0-9]')
这取决于NLS_SORT
参数的值。例如,在我的数据库中,它设置为CROATIAN
结果是您想要的:
SQL> select * From nls_session_parameters where parameter = 'NLS_SORT';
PARAMETER VALUE
-------------------- --------------------
NLS_SORT CROATIAN
SQL> with test (data) as
2 (select '1' from dual union all
3 select '2' from dual union all
4 select 'A' from dual union all
5 select '3' from dual union all
6 select '4' from dual union all
7 select 'B' from dual
8 )
9 select data
10 from test
11 order by data;
D
-
A
B
1
2
3
4
6 rows selected.
但是,在您的数据库中,NLS_SORT = BINARY
.让我们试试吧:
SQL> alter session set nls_sort = 'BINARY';
Session altered.
SQL> with test (data) as
2 (select '1' from dual union all
3 select '2' from dual union all
4 select 'A' from dual union all
5 select '3' from dual union all
6 select '4' from dual union all
7 select 'B' from dual
8 )
9 select data
10 from test
11 order by data;
D
-
1
2
3
4
A
B
6 rows selected.
SQL>
对;错误的结果。
因此,如果可以选择,请修改NLS_SORT
。按以下方式查看有效值
SQL> select * From v$nls_valid_values where parameter = 'SORT';
PARAMETER VALUE ISDEP
-------------------- -------------------- -----
SORT BINARY FALSE
SORT WEST_EUROPEAN FALSE
SORT XWEST_EUROPEAN FALSE
SORT GERMAN FALSE
<snip>
我相信下面的两个选项之一应该有效,
- 在
order by
子句中显式使用 NLSSORT 函数。
select * from my_table order by NLSSORT(data,'NLS_SORT=BINARY');
- 基于
- 列创建基于索引的函数,
create index mytable_nlssort_index on my_table(nlssort(data, 'nls_sort=''BINARY'''))
在为列编制索引时使用如下所示的查询。
select data from my_table order by data;