Select * FROM Product_component_version 1 NLSRTL 10.2.0.1.0 Production
2 Oracle Database 10g Enterprise Edition 10.2.0.1.0 64bi
3 PL/SQL 10.2.0.1.0 Production
4 TNS for IBM/AIX RISC System/6000: 10.2.0.1.0 Productio
--2: 150|433199|001|ZX433199120814165311|1
--pass
select nvl(max(SUBSTR(2,INSTR(2,'|', 1, 4))),0)+1 a_num
from cpabg1.tb_pay_commi_info
--ORA-01722: invaild number
select nvl(max(SUBSTR(2,INSTR(2,'|', 1, 4))),0) a_num
from cpabg1.tb_pay_commi_info--pass
select to_number(SUBSTR(2,INSTR(2,'|', 1, 4))) a_num
from cpabg1.tb_pay_commi_info
--ORA-01722: invaild number
select max(to_number(SUBSTR(2,INSTR(2,'|', 1, 4)))) a_num
from cpabg1.tb_pay_commi_infowho can tell me ,why?[
2 Oracle Database 10g Enterprise Edition 10.2.0.1.0 64bi
3 PL/SQL 10.2.0.1.0 Production
4 TNS for IBM/AIX RISC System/6000: 10.2.0.1.0 Productio
--2: 150|433199|001|ZX433199120814165311|1
--pass
select nvl(max(SUBSTR(2,INSTR(2,'|', 1, 4))),0)+1 a_num
from cpabg1.tb_pay_commi_info
--ORA-01722: invaild number
select nvl(max(SUBSTR(2,INSTR(2,'|', 1, 4))),0) a_num
from cpabg1.tb_pay_commi_info--pass
select to_number(SUBSTR(2,INSTR(2,'|', 1, 4))) a_num
from cpabg1.tb_pay_commi_info
--ORA-01722: invaild number
select max(to_number(SUBSTR(2,INSTR(2,'|', 1, 4)))) a_num
from cpabg1.tb_pay_commi_infowho can tell me ,why?[
返回的是 |1,所以不能+1同理,
SUBSTR(2,INSTR(2,'|', 1, 4))返回的是 |1,所以不能to_number().
的值是‘|1’,自然是不能+1.
同理,‘|1’也不能to_number!!!