用select to_number(nvl(substr(HC,INSTR(HC,'#')+1,10),0)) from Tz_Xdxxb WHERE xlmc='05' and xd=201做查询,查询结果为 1然而用select xlmc,xd from Tz_Xdxxb WHERE to_number(nvl(substr(HC,INSTR(HC,'#')+1,10),0))=1做查询,查询就报“无效数字”错误何解??高分求助!
调试欢乐多
加上条件呢:
select xlmc,xd from Tz_Xdxxb WHERE to_number(nvl(substr(HC,INSTR(HC,'#')+1,10),0))=1 and xlmc='05' and xd=201;
select substr(HC,INSTR(HC,'#')+1,10) from Tz_Xdxxb
select substr(HC,INSTR(HC,'#')+1,10) from Tz_Xdxxb WHERE xlmc='05' and xd=201
存在非数字的字符串
--nvl()要求字段类型一致 你这种还是用decode比较好
select xlmc,xd
from Tz_Xdxxb
WHERE to_number(decode(substr(HC,INSTR(HC,'#')+1,10),null,0))=1
substr(HC,INSTR(HC,'#')+1,10)确实是有为空的存在,所有我用了NVL把空的转为0to dawugui:
同上to wkc168
查询结果为1,只有1条记录
to zhuomingwang:
用 decode确实不报错了,但查不出结果
--估计你的hc字段是定长的,所以导致这样的结果
select count(nvl(substr(HC,INSTR(HC,'#')+1,10),0)) from Tz_Xdxxb WHERE xlmc='05' and xd=201
看有多少记录--试试这样
select xlmc,xd from Tz_Xdxxb
WHERE instr(trim(substr(HC,INSTR(HC,'#')+1,10)),'1')>0--or
select xlmc,xd from Tz_Xdxxb
WHERE to_number(nvl(substr(trim(HC),INSTR(HC,'#')+1,10),0))=1
这是不够的,nvl只能把null转为0,还在不能转为数字的字符串呢。建议改成以下代码:select xlmc,xd from Tz_Xdxxb WHERE nvl(substr(HC,INSTR(HC,'#')+1,10),'0')='1'
WHERE
to_number(nvl(substr(HC,INSTR(HC,'#')+1,10),0))>=1
and
to_number(nvl(substr(HC,INSTR(HC,'#')+1,10),0))<100
所以呢,to_number不用不行啊
你得确定这点啊,如果这个查询出来的结果又非数字的且不为空,那么怎么跟数字1,100作比较啊,就会报无效数字错误!
你先确认下。
再把你具体需求那些说明白一点,整理一下。
SELECT t.hc from Tz_Xdxxb t where nvl(substr(HC,INSTR(HC,'#')+1,10),'0')<>'0'
出来5条记录,4条符合规则,1条不符合规则,修改这条记录后,错误消失了!谢谢!