Question

求解下ORACLE对varchar2的between处理方式。望解答！

用字符类型查询时，结果如下：
SQL>select * from NOSECT where status=1 and '4615217182627' between beginno and endno;
RECID BEGINNO ENDNO STATUS
----- ------------- ------------- ------
4 40000000000 49999999999 1
8014 4615217182000 4615217182999 1
2 rows selected.
ZHHSC@CSHSC >exit

用数字类型查询时结果如下：
SQL>select * from nosect where 4615217182627 between beginno and endno;
RECID BEGINNO ENDNO STATUS
----- ------- ----- ------
0 rows selected.
求解，为什么会出现这样的结果？BEGINNO和ENDNO均为varchar2类型。Oracle对varchar2的between会怎么处理的。谢谢！

Answer 1

那你转换一下就好了，select * from nosect where to_char(4615217182627) between beginno and endno;

Answer 2

varchar2型比较时，会按字符从第一个开始比较，按字符ascii顺序比较

Answer 3

字符的比较是通过asc码来比较的从第一个字符开始比较如果相等再比较下一个

Answer 4

select * from NOSECT where status=1 and to_number(beginno) between 1 and 14615217182627

Answer 5

AscII码比较的