varchr2(40) 与 number类型怎么才能判断是否想等？怎么格式化之后比较？

Question

varchr2(40)  与 number类型怎么才能判断是否想等？
varchr2(40) 里面也是数字，不知道这两个类型要怎么格式化之后比较？
只判断整数部分是否相同

Answer 1

SQL> select * from test;未选定行SQL> insert into test values(1,'1.2');已创建 1 行。SQL> insert into test values(2,'2');已创建 1 行。SQL> insert into test values(2,'2.0');已创建 1 行。SQL> insert into test values(3,'3.7');已创建 1 行。SQL> commit;提交完成。SQL> select * from test;        ID NAME
---------- ----------
         1 1.2
         2 2
         2 2.0
         3 3.7SQL> select * from test where id=name;        ID NAME
---------- ----------
         2 2
         2 2.0SQL> select * from test id = to_number(name);
select * from test id = to_number(name)
                      *
ERROR 位于第 1 行:
ORA-00933: SQL 命令未正确结束
SQL> select * from test where id = to_number(name);        ID NAME
---------- ----------
         2 2
         2 2.0SQL> select * from test where id= trunc(to_number(name));        ID NAME
---------- ----------
         1 1.2
         2 2
         2 2.0
         3 3.7SQL>

Answer 2

比较的时候 oracle 默认会进行类型转换 SQL> select num1,
  2         num2,
  3         case
  4           when decode(instr(num1, '.'),
  5                       0,
  6                       num1,
  7                       substr(num1, 0, instr(num1, '.') - 1)) = trunc(num2) then
  8            '=='
  9           else
 10            '<>'
 11         end
 12    from t1
 13  ;NUM1         NUM2        CASEWHENDECODE(INSTR(NUM1,'.')
------------ --------------------- ------------------------------
3.251201                                                     3 ==
3.251201                                                     3 ==
3.251201                                                     3 ==
354401                                                       3 <>
354401                                                  354401 ==

Answer 3

SQL> select num1,
  2         num2,
  3         case
  4           when trunc(num1) = trunc(num2) then
  5            '=='
  6           else
  7            '<>'
  8         end
  9    from t1
 10  /NUM1                                                      NUM2 CASEWHENTRUNC(NUM1)=TRUNC(NUM2
---------------------------------------- --------------------- ------------------------------
3.251201                                                     3 ==
3.251201                                                     3 ==
3.251201                                                     3 ==
354401                                                       3 <>
354401                                                  354401 ==

Answer 4

number强行转化为charto_char(number)

Answer 5

SQL> create table t1(num1 varchar2(40), num2 number(20));Table createdSQL> insert into t1(num1, num2) values('3.251201', 3.2512);1 row insertedSQL> insert into t1(num1, num2) values('3.251201', 3.0);1 row insertedSQL> insert into t1(num1, num2) values('3.251201', 3);1 row insertedSQL> insert into t1(num1, num2) values('354401', 3);1 row insertedSQL> insert into t1(num1, num2) values('354401', 354401.42);1 row inserted简单的做法就是直接trunc(col1)和trunc(col2)进行比较就可以了

Answer 6

直接转成number类型比较更快呢