isnumeric,该如何解决

isnumeric

SQL code


select case when isnumeric(a)=1 and a>11.0 then 1 else 0 end 
 from # 
--Error converting data type varchar to numeric.

前面不是判断isnumeric了吗？

------解决方案--------------------

SQL code

select case when  a>11.0 then 1 else 0 end 
from #
where isnumeric(a)=1

------解决方案--------------------

探讨
SQL code

select case when a>11.0 then 1 else 0 end
from #
where isnumeric(a)=1

------解决方案--------------------
select case when cast(a as decimal(18,1))>11.0 then 1 else 0 end from # where isnumeric(a)=1

------解决方案--------------------
a字段什么类型？
------解决方案--------------------
select case when cast(a as decimal(18,1))>11.0 then 1 else 0 end from # where isnumeric(a)=1

select case when isnumeric(a)=1 and cast(a as decimal(18,1))>11.0 then 1 else 0 end from #

------解决方案--------------------
select case when a>11.0 then 1 else 0 end from # where isnumeric(a)=1
------解决方案--------------------

SQL code

CREATE TABLE #(a NVARCHAR(10))

INSERT # SELECT 1
INSERT # SELECT 'a'
INSERT # SELECT '.'--isnumeric(a)=1
INSERT # SELECT ','--isnumeric(a)=1

go

select isnumeric(a),case when (isnumeric(a)=1 AND a NOT LIKE '%[,]%' AND a<>'.')and cast(a as decimal(18,1))>11.0 then 1 else 0 end from #  

DROP TABLE #

------解决方案--------------------

探讨
SQL code
CREATE TABLE #(a NVARCHAR(10))

INSERT # SELECT 1
INSERT # SELECT 'a'
INSERT # SELECT '.'--isnumeric(a)=1
INSERT # SELECT ','--isnumeric(a)=1

go

select isnumeric(a),case when (isn……

相关推荐