各位大侠: 请赶快帮我解决,小弟不胜感激.
用的是oracle 9i,有一张表(t_card),其中有一个字段(card_num varchar2(20)),存放的是卡号
卡号一定有20位长,但有可能是这种情况(00000000000000000001),但又不能只写个1,所以该
字段就只能又varchar2(20).可现在的问题是:客户要求输入一个开始卡号和结束卡号,要求查出
这一段里又哪些卡。比如
select * from t_card a where a.card_num between '800000000000000000000' and '800000000000000000100'
可是这样的Sql 只会出来10条数据,但我仔细查了数据库,这一段之间又101条数据啊。我又用这样的
sql,还是不行 select * from t_card a where
to_number(a.card_num) between to_number('800000000000000000000') and to_number('800000000000000000100')
而且还用不上索引
请高手指点啊,如果该字段一定是varchar2(20)的话,请问应该怎样写SQl啊,谢谢谢谢谢谢了!
用的是oracle 9i,有一张表(t_card),其中有一个字段(card_num varchar2(20)),存放的是卡号
卡号一定有20位长,但有可能是这种情况(00000000000000000001),但又不能只写个1,所以该
字段就只能又varchar2(20).可现在的问题是:客户要求输入一个开始卡号和结束卡号,要求查出
这一段里又哪些卡。比如
select * from t_card a where a.card_num between '800000000000000000000' and '800000000000000000100'
可是这样的Sql 只会出来10条数据,但我仔细查了数据库,这一段之间又101条数据啊。我又用这样的
sql,还是不行 select * from t_card a where
to_number(a.card_num) between to_number('800000000000000000000') and to_number('800000000000000000100')
而且还用不上索引
请高手指点啊,如果该字段一定是varchar2(20)的话,请问应该怎样写SQl啊,谢谢谢谢谢谢了!
between '80000000000000000000' and '80000000000000000010'
2 (
3 VALUE VARCHAR2(20)
4 )
5 /Table createdSQL>
SQL> begin
2 for i in 1..100
3 loop
4 insert into testtab values (lpad(to_char(i),20,'0'));
5 end loop;
6 end;
7 /PL/SQL procedure successfully completedSQL> commit;Commit completeSQL> select * from testtab;VALUE
--------------------
00000000000000000001
00000000000000000002
00000000000000000003
00000000000000000004
00000000000000000005
00000000000000000006
00000000000000000007
00000000000000000008
00000000000000000009
00000000000000000010
00000000000000000011
00000000000000000012
00000000000000000013
00000000000000000014
00000000000000000015
00000000000000000016
00000000000000000017
00000000000000000018
00000000000000000019
00000000000000000020VALUE
--------------------
00000000000000000021
00000000000000000022
00000000000000000023
00000000000000000024
00000000000000000025
00000000000000000026
00000000000000000027
00000000000000000028
00000000000000000029
00000000000000000030
00000000000000000031
00000000000000000032
00000000000000000033
00000000000000000034
00000000000000000035
00000000000000000036
00000000000000000037
00000000000000000038
00000000000000000039
00000000000000000040
00000000000000000041VALUE
--------------------
00000000000000000042
00000000000000000043
00000000000000000044
00000000000000000045
00000000000000000046
00000000000000000047
00000000000000000048
00000000000000000049
00000000000000000050
00000000000000000051
00000000000000000052
00000000000000000053
00000000000000000054
00000000000000000055
00000000000000000056
00000000000000000057
00000000000000000058
00000000000000000059
00000000000000000060
00000000000000000061
00000000000000000062VALUE
--------------------
00000000000000000063
00000000000000000064
00000000000000000065
00000000000000000066
00000000000000000067
00000000000000000068
00000000000000000069
00000000000000000070
00000000000000000071
00000000000000000072
00000000000000000073
00000000000000000074
00000000000000000075
00000000000000000076
00000000000000000077
00000000000000000078
00000000000000000079
00000000000000000080
00000000000000000081
00000000000000000082
00000000000000000083VALUE
--------------------
00000000000000000084
00000000000000000085
00000000000000000086
00000000000000000087
00000000000000000088
00000000000000000089
00000000000000000090
00000000000000000091
00000000000000000092
00000000000000000093
00000000000000000094
00000000000000000095
00000000000000000096
00000000000000000097
00000000000000000098
00000000000000000099
00000000000000000100100 rows selectedSQL> select * from testtab where value between '0000000000000000000' and '0000000000000000100';VALUE
--------------------
00000000000000000001
00000000000000000002
00000000000000000003
00000000000000000004
00000000000000000005
00000000000000000006
00000000000000000007
00000000000000000008
00000000000000000009
00000000000000000010
00000000000000000011
00000000000000000012
00000000000000000013
00000000000000000014
00000000000000000015
00000000000000000016
00000000000000000017
00000000000000000018
00000000000000000019
00000000000000000020VALUE
--------------------
00000000000000000021
00000000000000000022
00000000000000000023
00000000000000000024
00000000000000000025
00000000000000000026
00000000000000000027
00000000000000000028
00000000000000000029
00000000000000000030
00000000000000000031
00000000000000000032
00000000000000000033
00000000000000000034
00000000000000000035
00000000000000000036
00000000000000000037
00000000000000000038
00000000000000000039
00000000000000000040
00000000000000000041VALUE
--------------------
00000000000000000042
00000000000000000043
00000000000000000044
00000000000000000045
00000000000000000046
00000000000000000047
00000000000000000048
00000000000000000049
00000000000000000050
00000000000000000051
00000000000000000052
00000000000000000053
00000000000000000054
00000000000000000055
00000000000000000056
00000000000000000057
00000000000000000058
00000000000000000059
00000000000000000060
00000000000000000061
00000000000000000062VALUE
--------------------
00000000000000000063
00000000000000000064
00000000000000000065
00000000000000000066
00000000000000000067
00000000000000000068
00000000000000000069
00000000000000000070
00000000000000000071
00000000000000000072
00000000000000000073
00000000000000000074
00000000000000000075
00000000000000000076
00000000000000000077
00000000000000000078
00000000000000000079
00000000000000000080
00000000000000000081
00000000000000000082
00000000000000000083VALUE
--------------------
00000000000000000084
00000000000000000085
00000000000000000086
00000000000000000087
00000000000000000088
00000000000000000089
00000000000000000090
00000000000000000091
00000000000000000092
00000000000000000093
00000000000000000094
00000000000000000095
00000000000000000096
00000000000000000097
00000000000000000098
00000000000000000099
00000000000000000100100 rows selectedSQL>
如果的你数据里面有的不足20个字符,你可以使用lpad(colname,20,'0') '0000000000000000000' and '0000000000000000100' 来判断!