ISNUMERIC 确定表达式是否为一个有效的数字类型。语法 ISNUMERIC ( expression )参数 expression要计算的表达式。返回类型 int注释 当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型,那么 ISNUMERIC 返回 1;否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。示例 A. 使用 ISNUMERIC 下面的示例返回 1,这是因为 zip 列包含有效的数值。 USE pubs SELECT ISNUMERIC(zip) FROM authors GOB. 使用 ISNUMERIC 和 SUBSTRING 下面的示例对于 titles 表中的所有书名都返回 0,这是因为没有一个书名是有效的数值。USE pubs GO -- Because the title column is all character data, expect a result of 0 -- for the ISNUMERIC function. SELECT SUBSTRING(title, 1, 15) type, price, ISNUMERIC(title) FROM titles GO下面是结果集:type price --------------- -------------------------- ----------- The Busy Execut 19.99 0 Cooking with Co 11.95 0 You Can Combat 2.99 0 Straight Talk A 19.99 0 Silicon Valley 19.99 0 The Gourmet Mic 2.99 0 The Psychology (null) 0 But Is It User 22.95 0 Secrets of Sili 20.00 0 Net Etiquette (null) 0 Computer Phobic 21.59 0 Is Anger the En 10.95 0 Life Without Fe 7.00 0 Prolonged Data 19.99 0 Emotional Secur 7.99 0 Onions, Leeks, 20.95 0 Fifty Years in 11.95 0 Sushi, Anyone? 14.99 0 (18 row(s) affected)
第一个sql语句的表名错了是TCalKey 发贴时的输写问题,不好意思
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 aaa> 0 -- 你的语句没看出错误.且有:ISNUMERIC(Fkey)=1 这个条件,不知道为何了.
select fkeyid,cast(fkey as float) aaa from TB where fcontrolname in ( select fcontrolname from tvalue where fvaluetype=1 ) and ISNUMERIC(Fkey)=1 这样不加外层的aaa> 0 条件判断就不会报错
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 aaa> 0 问题可能出在红字所标处, 可以改成:cast(fkey as float)>0,因为aaa不可以直接作条件的
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 aaa>0 注释掉--and ISNUMERIC(Fkey)=1问题依旧存在 将数据类型 varchar 转换为 float 时出错。
这里也有出错的可能: select fkeyid,cast(fkey as float) aaa from TCalKey
比如select ISNUMERIC('$')1select cast('$' as float)从数据类型 varchar 转换为 float 时出错
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 * 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
select * from TCalKey where fcontrolname in ( select fcontrolname from tvalue where fvaluetype=1 ) and ISNUMERIC(Fkey)=1 and cast(fkey as float)>0 一开始就是用的这种输写方式单同样报错,所以才单独使用了下面语句调试 select fkeyid,cast(fkey as float) aaa from TCalKey where fcontrolname in ( select fcontrolname from tvalue where fvaluetype=1 ) and ISNUMERIC(Fkey)=1 上面这个语句可以转换执行正常,但只要加上外面的条件aaa>0 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 aaa>0 就不行了:将数据类型 varchar 转换为 float 时出错。
where cast(aaa as float)> 0 ?
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')这就是问题所在
会抱错吗?
确定表达式是否为一个有效的数字类型。语法
ISNUMERIC ( expression )参数
expression要计算的表达式。返回类型
int注释
当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型,那么 ISNUMERIC 返回 1;否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。示例
A. 使用 ISNUMERIC
下面的示例返回 1,这是因为 zip 列包含有效的数值。 USE pubs
SELECT ISNUMERIC(zip)
FROM authors
GOB. 使用 ISNUMERIC 和 SUBSTRING
下面的示例对于 titles 表中的所有书名都返回 0,这是因为没有一个书名是有效的数值。USE pubs
GO
-- Because the title column is all character data, expect a result of 0
-- for the ISNUMERIC function.
SELECT SUBSTRING(title, 1, 15) type, price, ISNUMERIC(title)
FROM titles
GO下面是结果集:type price
--------------- -------------------------- -----------
The Busy Execut 19.99 0
Cooking with Co 11.95 0
You Can Combat 2.99 0
Straight Talk A 19.99 0
Silicon Valley 19.99 0
The Gourmet Mic 2.99 0
The Psychology (null) 0
But Is It User 22.95 0
Secrets of Sili 20.00 0
Net Etiquette (null) 0
Computer Phobic 21.59 0
Is Anger the En 10.95 0
Life Without Fe 7.00 0
Prolonged Data 19.99 0
Emotional Secur 7.99 0
Onions, Leeks, 20.95 0
Fifty Years in 11.95 0
Sushi, Anyone? 14.99 0 (18 row(s) affected)
发贴时的输写问题,不好意思
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 aaa> 0 --
你的语句没看出错误.且有:ISNUMERIC(Fkey)=1 这个条件,不知道为何了.
where fcontrolname in
(
select fcontrolname from tvalue where fvaluetype=1
)
and ISNUMERIC(Fkey)=1
这样不加外层的aaa> 0 条件判断就不会报错
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 aaa> 0 问题可能出在红字所标处,
可以改成:cast(fkey as float)>0,因为aaa不可以直接作条件的
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 aaa>0
注释掉--and ISNUMERIC(Fkey)=1问题依旧存在
将数据类型 varchar 转换为 float 时出错。
这里也有出错的可能:
select fkeyid,cast(fkey as float) aaa from TCalKey
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 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
where fcontrolname in
(
select fcontrolname from tvalue where fvaluetype=1
)
and ISNUMERIC(Fkey)=1
and cast(fkey as float)>0
一开始就是用的这种输写方式单同样报错,所以才单独使用了下面语句调试
select fkeyid,cast(fkey as float) aaa from TCalKey
where fcontrolname in
(
select fcontrolname from tvalue where fvaluetype=1
)
and ISNUMERIC(Fkey)=1
上面这个语句可以转换执行正常,但只要加上外面的条件aaa>0
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 aaa>0
就不行了:将数据类型 varchar 转换为 float 时出错。
?
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')这就是问题所在
自己写一个判断是否为数字型的函数吧.