declare @a varchar(50)
select @a='5.00620000'
select left(@a,charindex('.',@a))+reverse(convert(varchar,convert(int,reverse(substring(@a,charindex('.',@a)+1,len(@a))))))
/*
5.0062
*/
select @a='5.00620000'
select left(@a,charindex('.',@a))+reverse(convert(varchar,convert(int,reverse(substring(@a,charindex('.',@a)+1,len(@a))))))
/*
5.0062
*/
select @a='5.00620000'
select left(@a,charindex('.',@a))+reverse(convert(varchar,convert(int,reverse(substring(@a,charindex('.',@a)+1,len(@a))))))
/*
5.0062
*/
declare @t table(col Decimal(18,8))
insert @t select 5.00620000
union all select 0.05300000
union all select 0.00000000
union all select 1.00000000
union all select 1.10000000--处理
select col=cast(cast(col as float) as varchar)
from @t/*--结果col
------------------------------
5.0062
0.053
0
1
1.1(所影响的行数为 5 行)
--*/
我考虑过将decimal(18,8)转换成float或real,可是当小数位数超过5位时,例如0.00005时,得到的是5e-005
declare @t table(col Decimal(18,8))
insert @t select 5.00620000
union all select 0.05300000
union all select 0.00000000
union all select 1.00000000
union all select 1.12345600
union all select 1.12345670
union all select 1.12345678--处理
select col=case
when patindex('%[^0]%.%',reverse(col))>0
then left(col,len(col)-patindex('%[^0]%.%',reverse(col)))
else cast(cast(col as float) as varchar) end
from @t/*--结果col
---------------------
5.006
0.05
0
1
1.12345
1.123456
1.1234567(所影响的行数为 7 行)
--*/