declare @n numeric(7,0)set @n=1234567.89select @n,cast(right(str(@n),5) as numeric(5,0))
create table test(col numeric(7,0)) insert test select 1234567.23 union all select 1234567 union all select 1234 union all select 1234.22select cast(stuff(cast(col as varchar),1,2,'') as numeric(5,0)) from testdrop table test------- 34567 34567 34 34(所影响的行数为 4 行)
--不建议用这种写法,因为如果不指定长度,系统会将超过30的截断 cast(col as varchar) 如:declare @s varchar(100)set @s='012345678901234567890123456789abc'select cast(@s as varchar) --只会显示:012345678901234567890123456789--而不会显示abc
declare @i numeric(7,0) set @i=78.00 select cast(@i as numeric(5,0))
insert test select 1234567.23
union all select 1234567
union all select 1234
union all select 1234.22select cast(stuff(cast(col as varchar),1,2,'') as numeric(5,0)) from testdrop table test-------
34567
34567
34
34(所影响的行数为 4 行)
--不建议用这种写法,因为如果不指定长度,系统会将超过30的截断
cast(col as varchar)
如:declare @s varchar(100)set @s='012345678901234567890123456789abc'select cast(@s as varchar)
--只会显示:012345678901234567890123456789--而不会显示abc
set @i=78.00
select cast(@i as numeric(5,0))