--处理示例--示例数据
declare @t table(num varchar(10))
insert @t select '1231.1'
union all select 'xcvx'
union all select '1312.4'
union all select '1231'--统计
select SUM(CASE WHEN isnumeric(num)=1 THEN cast(num as float) ELSE 0 END) AS exp1
from @t--结果: 3774.5
declare @t table(num varchar(10))
insert @t select '1231.1'
union all select 'xcvx'
union all select '1312.4'
union all select '1231'--统计
select SUM(CASE WHEN isnumeric(num)=1 THEN cast(num as float) ELSE 0 END) AS exp1
from @t--结果: 3774.5
SUM(CASE WHEN isnumeric(num)=1 THEN num ELSE 0 END)
改为
SUM(CASE WHEN isnumeric(num)=1 THEN Cast(num As Float)ELSE 0 END)
Insert TEST Values('1231.1')
Insert TEST Values('xcvx')
Insert TEST Values('1312.4')
Insert TEST Values('1231')
GO
Select SUM(CASE WHEN isnumeric(num)=1 THEN Cast(num As Float)ELSE 0 END) from TEST
GO
Drop table TEST
GO
--结果
3774.5