数据库报“ORA-01722: 无效数字”错误的解决办法

报错信息：ORA-01722: 无效数字

问题SQL:

?

1 2 3 4 5 6 7 8 9 10 11

SELECT A.*, B.FUND_CODE AS FUND_CODE,  B.FUND_FULLNAME AS Fund_Fullname FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B WHERE A.ENTITY_ID=B.FUND_ID   /*     此处A.ENTITY_ID的类型为VARCHAR;     B.FUND_ID的类型为NUMBER;     两者类型不匹配所以报错。 */

---

更改后正确SQL:

?

1 2 3 4 5 6 7 8 9

SELECT A.*, B.FUND_CODE AS FUND_CODE,  B.FUND_FULLNAME AS Fund_Fullname FROM BSP_SYS_ATTACH A,BSP_FUND_BASE B WHERE A.ENTITY_ID=TO_CHAR(B.FUND_ID)   /*     类型转换函数to_char（） */

---

拓展：

将日期型转换为字符串TO_CHAR()

?

1 2 3

SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS AM') FROM DUAL

将数字型转换为字符串TO_CHAR()

?

1 2 3

SELECT TO_CHAR(123.45678,'$99999.999') FROM DUAL;

将字符换转换为日期TO_DATE()

?

1 2 3

SELECT TO_DATE ('20100913', 'YYYY-MM-DD') FROM DUAL;

将字符串转换为数字TO_NUMBER()

?

1 2 3

SELECT TO_NUMBER('01') FROM DUAL;