sql问题。

Question

数据库中有个字段QZFW是varchar类型，存放的数据格式是"数字编号"-"数字编号"  如：0012-1345  现在我想输入个条件a,使字段满足   QZFW中'-'前面的数字<=  a <= QZFW中‘-’后面的数字。。
我写的sql如下：55000是条件a,,,select * from (select to_number(substr(QZFW,0,instr(QZFW,'-')-1)) nbefore,
to_number(substr(QZFW,instr(QZFW,'-')+1,length(QZFW))) nafter,
QZFW as qzfw_C from TBL_01 Where  translate(QZFW,'\1234567890-','\') is null and (length(QZFW) - length(replace(QZFW,'-')))/length('-')=1) t
where t.nbefore<=55000 and t.nafter >=55000
但是如果输入条件太大如：304000582 就会报“ORA-01722: 无效数字”错误

Answer 1

可能你的QZFW除了'-'线外，不能转换为数字的非法数据。
另外，这个取后面部分的数字，不需要length(QZFW)
to_number(substr(QZFW,instr(QZFW,'-')+1))

Answer 2

条件小不会报错。。数据我都检查过了，都没有问题。。translate(QZFW,'\1234567890-','\') is null 就是将所有的  数字-数字 格式的数据都查出来，将含其他字符的过滤掉

Answer 3

(length(QZFW) - length(replace(QZFW,'-')))/length('-')=1)  这个的目的是确保 QZFW字段包含一个'-'字符

Answer 4

(length(QZFW) - length(replace(QZFW,'-')))/length('-')=1) 这个的目的是确保 QZFW字段包含一个'-'字符

Answer 5

数据应该有问题！
是否存在：
-8345
345-
这样的数据？？？？？试下增加条件：
 and instr(QZFW,'-') > 1
 and instr(QZFW,'-') < length(QZFW)

Answer 6

加上条件了，数小了可以，打了还是报错。
select * from (select to_number(substr(QZFW,0,instr(QZFW,'-')-1)) nbefore,
to_number(substr(QZFW,instr(QZFW,'-')+1)) nafter,
QZFW as qzfw_C from T_C602BC0C604B4 Where  instr(QZFW,'-') > 1
 and instr(QZFW,'-') < length(QZFW)and  translate(QZFW,'\1234567890-','\') is null and (length(QZFW) - length(replace(QZFW,'-')))/length('-')=1) t
where t.nbefore<=222  and t.nafter >=222

Answer 7

我新建一个表tt,将原来表中的QZFW数据都导入到新的表中，就可以还是使用以前的条件就没有问题。。
insert into tt(id)  select qzfw_c  from (select to_number(substr(QZFW,0,instr(QZFW,'-')-1)) nbefore,
to_number(substr(QZFW,instr(QZFW,'-')+1)) nafter,
QZFW as qzfw_C from T_C602BC0C604B4 Where  instr(QZFW,'-') > 1
 and instr(QZFW,'-') < length(QZFW)and  translate(QZFW,'\1234567890-','\') is null and (length(QZFW) - length(replace(QZFW,'-')))/length('-')=1) t
在新表中查询没有问题。。
select *  from (select to_number(substr(id,0,instr(id,'-')-1)) nbefore,
to_number(substr(id,instr(id,'-')+1)) nafter,
id as qzfw_C from tt Where  instr(id,'-') > 1
 and instr(id,'-') < length(id)and  translate(id,'\1234567890-','\') is null and (length(id) - length(replace(id,'-')))/length('-')=1) t
 where t.nbefore<=5501098080 and t.nafter>=5501098080

Answer 8

最终使用的字符比较解决了问题。。都转换为相同长度的字符，然后比较。基本的sql如下：
select *  from
  (select QZFW as qzfw_C,
                 lpad((substr(QZFW, 0, instr(QZFW, '-') - 1)),13,'0') as nbefore,
                 lpad((substr(QZFW, instr(QZFW, '-') + 1)),13,'0') as nafter
            from T_C602BC0C604B4
           Where translate(QZFW, '\1234567890-', '\') is null
             and (length(QZFW) - length(replace(QZFW, '-'))) / 1 = 1
    )   where nbefore<=lpad(550109808022,13,'0') and nafter>=lpad(550109808022,13,'0')