＋设为首页 ＋添加到收藏夹
表名:T_1
col_1 varchar2 primary key
col_2 varchar2 (该字段可能是非数字字符，也可能是数字字符)
…… ……
要求T_1中，所有col_2是数字字符并且col_2 > 100的记录
类似：
1.select col_1 from T_1
where isnumber(col_2) = 1 and to_number(col_2) > 100
2.select col_1 from T_1
where col_2 > 100 and isnumber(col_2) = 1
3.select * from (select to_number(col_2) A from T1
where isnumber(col_2) = 1)
where A > 100
以上语句都已经经过测试，当col_2包含类似a这样的非数字字符时,该sql大概在to_number(col_2)>100处出错.
select col_1,col_2
from(
select col_1,
(case when isnumber(col_2) = 1 then to_number(col_2) else 1 end) as col_2
from t_1 )
where col_2>100
试看看,我也没机会试!
修改一下:
select c.col1,c.col2
from (
select a.col1,a.col2 from t_a a
where exist (select 1 from t_a b where isnumber(b.col_2) = 1 and a.col2=b.col2)
) c
where to_number(c.col2)>100;
