前面包含数字时10g订购varchar列 [英] 10g ordering varchar columns when containing numbers in front

问题描述

我有一个Oracle 10g数据库，并且有一个VARCHAR2(2000个字符)列，可以将其命名为TEST，例如可以在前面包含数字:

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:

test
1test
3test

当我叫"...按TEST asc排序"或简称为"...按TEST排序"

When I call "... order by TEST asc" or simply "... order by TEST"

我得到的结果像

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?

推荐答案

您的

What is your NLS_SORT set to? (select sys_context('USERENV', 'NLS_SORT') from dual). If it is BINARY then 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.

您可以通过修改该参数在数据库或会话级别更改排序顺序，但也可以为单个查询更改它:

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')

根据您的字符集，您可能需要尝试使用不同的值而不是BINARY.您可以使用select value from v$nls_valid_values where parameter = 'SORT'获取所有有效值的列表.但是请注意，NLS_SORT文档中提到了潜在的性能影响.

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.

nlssort()函数在此处.

这篇关于前面包含数字时10g订购varchar列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！