Oracle使用混合varchar列但使用数字where子句对结果进行排序

问题描述

我有一个带有VARCHAR2列的表，其中包含纯数字和字母数字的混合值.我有一个CODE列，其中包含:

I have a table with a VARCHAR2 column which contains values that are a mixture of pure-numbers and alpha-numerics. I have a CODE column that contains:

200
215
220
A553
D545
etc.

以下查询有效:

select *
from   TABLE
where  CLASS = 3
AND    (CODE >= 210 and CODE < 220) or CODE = 291)

CLASS 3的值始终是数字.

Values that are CLASS 3 are always numeric.

但是当我添加ORDER BY时，它不起作用:

But when I add a ORDER BY, it doesn't work:

select *
from   TABLE
where  CLASS = 3
and    (CODE >= 210 and CODE < 220) or CODE = 291)
ORDER BY CODE

相反，我得到了ORA-01722: invalid number.此似乎是，因为Oracle优化器正在评估where子句之前的"order by" ，因此可以评估非数字值.

instead I get ORA-01722: invalid number. This seems to be because the Oracle Optimiser is assessing the "order by" before the where clause, and thus non-numeric values get assessed.

我尝试将其更改为ORDER BY TO_CHAR(CODE)，但没有任何影响.尝试将所有内容放入子查询中的结果类似.

I have tried changing it to ORDER BY TO_CHAR(CODE) but to no affect. Similar negative result with trying to place it all into a sub-query.

那么，如何按CODE(ASC结尾)对查询结果进行排序?我想我可以在where子句中手动将所有可能的CODE值指定为字符串(即code = '210' or code = '211' or...)，但是还有一种更优雅的方法吗?

So, how do I order the results of this query by CODE (ASCending)? I guess I can specify all possible CODE values manually in the where clause as strings (i.e. code = '210' or code = '211' or...), but is there a more elegant way?

推荐答案

问题可能出在您的WHERE条件下，因为这迫使Oracle将代码强制转换为数字；

The problem can be in your WHERE condition, given that it forces Oracle to cast your code to number;

尝试将WHERE条件保持为varchar2格式:

Try keeping the WHERE condition in varchar2 format:

with TABLE_(code, class_) as
(
select '200',3 from dual union all
select '215',3 from dual union all
select '220',3 from dual union all
select 'A553',3 from dual union all
select 'D545',3 from dual
)
select *
from   TABLE_
where  CLASS_ = 3
and   ( (CODE >= '210' and CODE < '220') or CODE = '291')
ORDER BY CODE

这篇关于Oracle使用混合varchar列但使用数字where子句对结果进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！