22,209
社区成员
发帖
与我相关
我的任务
分享
create table #t(fkeyid int,fkey varchar(10))
insert into #t select 1,''
insert into #t select 1,null
insert into #t select 1,'a'
insert into #t select 1,'5'
insert into #t select 1,'57'
insert into #t select 1,'57e4'
select fkeyid,cast(fkey as float) aaa from #t where ISNUMERIC(Fkey)=1
------------------
1 5
1 57
1 570000
select * from (
select fkeyid,cast(fkey as float) aaa from #t where ISNUMERIC(Fkey)=1
)a where aaa>5
------------------------
消息 8114,级别 16,状态 5,第 1 行
从数据类型 varchar 转换为 float 时出错。
select isnumeric('57e4')
select isnumeric('57e4e')
select * from
(
select fkeyid,cast(fkey as float) aaa from TCalKey
where fcontrolname in
(
select fcontrolname from tvalue where fvaluetype=1
)
and ISNUMERIC(Fkey)=1
) as TB1
where cast(fkey as float) > 0
set nocount on
select fkeyid,cast(fkey as float) aaa into #t from TCalKey
where fcontrolname in
(
select fcontrolname from tvalue where fvaluetype=1
)
and ISNUMERIC(Fkey)=1
)
select * from #t where aaa> 0
drop table #t
比如
select ISNUMERIC('$')
1
select cast('$' as float)
从数据类型 varchar 转换为 float 时出错
这里也有出错的可能:
select fkeyid,cast(fkey as float) aaa from TCalKey