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
select case when cast(a as decimal(18,1))>11.0 then 1 else 0 end from # where isnumeric(a)=1select 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
小数点惹得isnumeric('.')=1是成立的,但是转成numeric又转不过去有好方法吗?
CREATE TABLE #(a NVARCHAR(10))INSERT # SELECT 1 INSERT # SELECT 'a' INSERT # SELECT '.'--isnumeric(a)=1 INSERT # SELECT ','--isnumeric(a)=1goselect 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 #
CREATE TABLE #(a NVARCHAR(10))INSERT # SELECT 1 INSERT # SELECT 'a' INSERT # SELECT '.'--isnumeric(a)=1 INSERT # SELECT ','--isnumeric(a)=1goselect isnumeric(a),case when (isnumeric(a)=1 AND a NOT LIKE '%[,]%')and (cast(CASE WHEN a as decimal(18,1))>11.0 OR a='.') then 1 else 0 end from # DROP TABLE #
改改打錯了 CREATE TABLE #(a NVARCHAR(10))INSERT # SELECT 1 INSERT # SELECT 'a' INSERT # SELECT '.'--isnumeric(a)=1 INSERT # SELECT ','--isnumeric(a)=1goselect isnumeric(a),case when (isnumeric(a)=1 AND a NOT LIKE '%[,]%')and ( a='.' OR cast(a as decimal(18,1))>11.0 ) then 1 else 0 end from # DROP TABLE #
注意 a='.'條件放的順序要在cast(a as decimal(18,1))前面
只能是这样了,还以为isnumeric可以搞定的。别的哪些字符还会出现这种状况呢?
isnumeric(a)=1 and a>11.0你只判斷了第一個a isnumeric(a)=1 and 後面那個你判斷了沒,沒 a>11.0 所以你要在where條件中選擇數據 。。
from #
where isnumeric(a)=1
INSERT # SELECT 'a'
INSERT # SELECT '.'--isnumeric(a)=1
INSERT # SELECT ','--isnumeric(a)=1goselect 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 #
CREATE TABLE #(a NVARCHAR(10))INSERT # SELECT 1
INSERT # SELECT 'a'
INSERT # SELECT '.'--isnumeric(a)=1
INSERT # SELECT ','--isnumeric(a)=1goselect isnumeric(a),case when (isnumeric(a)=1 AND a NOT LIKE '%[,]%')and (cast(CASE WHEN a as decimal(18,1))>11.0 OR a='.') then 1 else 0 end from # DROP TABLE #
改改打錯了
CREATE TABLE #(a NVARCHAR(10))INSERT # SELECT 1
INSERT # SELECT 'a'
INSERT # SELECT '.'--isnumeric(a)=1
INSERT # SELECT ','--isnumeric(a)=1goselect isnumeric(a),case when (isnumeric(a)=1 AND a NOT LIKE '%[,]%')and ( a='.' OR cast(a as decimal(18,1))>11.0 ) then 1 else 0 end from # DROP TABLE #