ORA-01722:无效数字(存储过程中出这个错误,比较蹊跷)

xgghxkhuang

CREATE TABLE TEST_CHAR2
(
A VARCHAR2(100)
);
INSERT INTO TEST_CHAR2
VALUES ('1300,1850');
COMMIT;
SELECT * FROM TEST_CHAR2
WHERE 1300 IN A ;
--报错，转换错误
SELECT to_number(CASE WHEN LEVEL=1 THEN SUBSTR(a,1,Instr(A, ',', 1, 1)-1)
       ELSE  SUBSTR(a,Instr(A, ',', 1, LEVEL-1)+1,Instr(A, ',', 1, LEVEL)-Instr(A, ',', 1, LEVEL-1)-1) END) FROM
(SELECT a||',' a FROM TEST_CHAR2) TEST_CHAR2
CONNECT BY LEVEL<= LENGTH(A)-LENGTH(Replace(A, ',', ''));
--现写了一个，用在逗号小于200的情况
with va as
( select 1300 id from dual
union all select 1850 from dual
)
select id from va where id in
(SELECT to_number(CASE WHEN LEVEL=1 THEN SUBSTR(a,1,Instr(A, ',', 1, 1)-1)
       ELSE  SUBSTR(a,Instr(A, ',', 1, LEVEL-1)+1,Instr(A, ',', 1, LEVEL)-Instr(A, ',', 1, LEVEL-1)-1) END) FROM
(SELECT a||',' a FROM TEST_CHAR2) TEST_CHAR2
CONNECT BY LEVEL<= LENGTH(A)-LENGTH(Replace(A, ',', '')))
--这样就不报错了