select itmcode from (
select * from itm_basic_mstr x
where isNumeric(x.UDV5) = 1
) as y
where cast(y.UDV5 as numeric(10,2)) BETWEEN 1 AND 10
select * from itm_basic_mstr x
where isNumeric(x.UDV5) = 1
) as y
where cast(y.UDV5 as numeric(10,2)) BETWEEN 1 AND 10
--会错
select 1 where 'adsfadsfsd' BETWEEN 1 AND 10--不会错
select 1 where isNumeric('asdfsdfs')=1 and 'adsfadsfsd' BETWEEN 1 AND 10
where isNumeric(x.UDV5) = 1 and x.UDV5 BETWEEN '1' AND '10'
where
(case when isNumeric(x.UDV5) = 1 then x.UDV5 else 0 end) BETWEEN 1 AND 10
insert @ values('a')
insert @ values(1)
insert @ values(4)
insert @ values('b')select * from @ where isNumeric(a) = 1 and a BETWEEN 1 AND 10
我这里可以哟!不要说你不行吧!a
----------
1
4
where
(case when (isNumeric(x.UDV5)=0 or x.UDV5 like '[.+-]') then 0 else x.UDV5 end) between 1 and 10
所以要用:select itmcode from itm_basic_mstr x
where
(case when (isNumeric(x.UDV5)=0 or x.UDV5 like '[.+-]') then 0 else x.UDV5 end) between 1 and 10
select itmcode from itm_basic_mstr x
where
(case when (isNumeric(x.UDV5)=0 or x.UDV5 like '[.+-]') then 0.0 else x.UDV5 end) between 1 and 10
select itmcode from itm_basic_mstr x
where
(case when (isNumeric(x.UDV5)=0 or x.UDV5 like '[.+-]') then 0.0 else x.UDV5 end) between 1 and 10to:大力你试试:
declare @ table (a varchar(10))
insert @ values('a')
insert @ values('0.1')
insert @ values('.')
insert @ values('+')
insert @ values('-')
insert @ values('0.76')select * from @ where isNumeric(a) = 1 and a BETWEEN 1 AND 10
我想会出错的。
where
(case when (isNumeric(x.UDV5)=0 or x.UDV5 like '[.+-]') then 0 else x.UDV5 end) between 1 and 10这样吧!!
insert @ values('a')
insert @ values(1)
insert @ values(4)
insert @ values('-')
insert @ values('.')
insert @ values('+')select * from @
where
(case when (isNumeric(a)=0 or a like '[.+-]') then 0 else a end) between 1 and 10
insert @ values('a')
insert @ values(1)
insert @ values(4)
insert @ values('-')
insert @ values('.')
insert @ values('+')select * from @ where (case when (isNumeric(a)=0 or a like '[.+-]') then 0 else a end) between 1 and 10a
----------
1
4select * from @ where (case when (isNumeric(a)=0 or a like '[.+-]') then 0.0 else a end) between 1 and 10服务器: 消息 8115,级别 16,状态 8,行 9
将 numeric 转换为数据类型 numeric 时发生算术溢出错误。
declare @ table (a varchar(10))
insert @ values('a')
insert @ values(1)
insert @ values(4)
insert @ values('-')
insert @ values('.')
insert @ values('+')
insert @ values('7.8')
insert @ values('8.222')出同样的错!
楼主出错可能有小数点
insert @ values('a')
insert @ values(1)
insert @ values(4)
insert @ values('-')
insert @ values('.')
insert @ values('+')
insert @ values('7.8')
insert @ values('8.222')select * from @ where (case when (isNumeric(a)=0 or a like '[.+-]') then 0.0 else cast(a as numeric(8,2)) end) between 1.0 and 10.0
万万没想到这么麻烦。
看来isnumeric 也成问题。这算不算BUG,我想算。
http://www.mscommunity.com/CommunityWish/WishList.aspx?Categoryid=7
去提这个bug我是管理员,我来推荐!!
1.
isnumeric('.')=1
isnumeric('+')=1
isnumeric('-')=12.
declare @ table (a varchar(10))
insert @ values('1')
insert @ values('4')
select * from @ where a between 1.0 and 10.0
--以上正确!但:declare @ table (a varchar(10))
insert @ values('-2666')
insert @ values('5.66')
insert @ values('+7.1')
insert @ values('7.8')
insert @ values('1.222')select * from @ where a between 1.0 and 10.0
--出错信息
--将 numeric 转换为数据类型 numeric 时发生算术溢出错误。
declare @ table (a varchar(10))
insert @ values('1')
insert @ values('4')
select * from @ where a between 1.0 and 10.0
--以上正确!但:declare @ table (a varchar(10))
insert @ values('-2666')
insert @ values('5.66')
insert @ values('+7.1')
insert @ values('7.8')
insert @ values('1.222')select * from @ where a between 1.0 and 10.0
--出错信息
--将 numeric 转换为数据类型 numeric 时发生算术溢出错误这个!!哦!你已经写在下面了,算了!