無法將varchar值100轉換為OracleSQL中等級列下的數字

我有一個標題為“等級”的列，定義為 VARCHAR2(3)；但是，當我的等級值為“100”時，Oracle SQL 下的系統無法將此值讀取為整數，并且我無法設定以下情況條件：

WHEN CAST(GRADE as varchar(3)) between '93' and '100' then 4.3 

更新：

我得到了價值的0分數，100而不是4.3

Select CRSE_NUMB,
       Grade,
       CASE 
       WHEN CAST(GRADE as varchar(3)) between '93' and '100' then 4.3 
       WHEN Grade < '60' then 0.0
       WHEN Grade = '60' then 1.0
       WHEN Grade between '61' and '62' then 1.3
       WHEN Grade between '79' and '82' then 3.3
       WHEN Grade between '83' and '86' then 3.7
       WHEN Grade between '87' and '92' then 4.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION error) between '93' and '100' then 4.3 
       else null
       end as Score
from   SWBGRDE
group by CRSE_NUMB, Grade

uj5u.com熱心網友回復：

使用TO_NUMBER()：

WHEN TO_NUMBER(grade) BETWEEN 93 AND 100 THEN 4.3

如果您的grade列中有非數字資料，并且在 Oracle 12 或更高版本上，您可以使用：

WHEN TO_NUMBER(grade DEFAULT NULL ON CONVERSION ERROR) BETWEEN 93 AND 100 THEN 4.3

如果您使用字串進行比較，則比較是在長度連續增加的子字串上進行的，因此它將比較第一個字符GRADE是否介于最小值'9'和最大值之間，而'1'其中的最大值永遠不會為真。

例如，在 Oracle 12 中，您可以使用：

SELECT grade,
       CASE
       WHEN TO_NUMBER(grade DEFAULT NULL ON CONVERSION ERROR) < 93 THEN 1
       WHEN TO_NUMBER(grade DEFAULT NULL ON CONVERSION ERROR) BETWEEN 93 AND 100 THEN 4.3
       END AS score
FROM   table_name

或者，在早期版本中，您可以查找非數字字符：

SELECT grade,
       CASE
       WHEN REGEXP_LIKE(grade, '\D') THEN NULL
       WHEN TO_NUMBER(grade) < 93 THEN 1
       WHEN TO_NUMBER(grade) BETWEEN 93 AND 100 THEN 4.3
       END AS score
FROM   table_name

然后，對于樣本資料：

CREATE TABLE table_name (grade VARCHAR2(3));

INSERT INTO table_name (grade)
SELECT TO_CHAR(90   LEVEL) FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 'AAA' FROM DUAL;

輸出：

年級	分數
91	1
92	1
93	4.3
94	4.3
95	4.3
96	4.3
97	4.3
98	4.3
99	4.3
100	4.3
AAA	空值

db<>在這里擺弄

關于您的更新：

Do not compare numbers as strings - compare them as numbers and use TO_NUMBER everywhere:

Select CRSE_NUMB,
       Grade,
       CASE 
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) < 60 then 0.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) = 60 then 1.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 62 then 1.3
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 82 then 3.3
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 86 then 3.7
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 92 then 4.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 100 then 4.3 
       else null
       end as Score
from   SWBGRDE
group by CRSE_NUMB, Grade

If appropriate, you can just specify the top end of the range and let the CASE expression short-circuit to the first match.

uj5u.com熱心網友回復：

不要在 93 和 100 左右使用引號：

SELECT CASE WHEN CAST(100 as varchar(3)) between 93 and 100 then 4.3 END FROM DUAL

但是由于您使用的是數字，所以我不確定您為什么要將其轉換為 varchar。有什么特別的原因嗎？

標籤：sql 甲骨文

上一篇：有沒有辦法CASEDD-MMM-YYYY日期格式？

下一篇：ORACLE：將字串拆分為多行