34,590
社区成员
发帖
与我相关
我的任务
分享
create table test(id decimal(18,3))
go
insert into test values(.902),(.3),(.5),(100.5)
go
select
case
when right(ltrim(id),1) = '0'
then substring(ltrim(id),1,len(ltrim(id))-1)
else
ltrim(id)
end result
from test
go
drop table test
go
(4 行受影响)
result
-----------------------------------------
0.902
0.30
0.50
100.50
(4 行受影响)
with t1(c1) as(
select convert(float, 0.902) union all
select 0.3 union all
select 0.5 union all
select 100.5)
SELECT CONVERT(VARCHAR(10),c1,3) FROM t1
/*
(无列名)
0.902
0.3
0.5
100.5
*/
你看这个 行不行
CREATE TABLE #TT1 (MM FLOAT)
INSERT INTO #TT1(MM) VALUES ('.902'),('.3'),('.5'),('100.5'),('100.507')
SELECT MM,WZ=LEN(MM)-CHARINDEX('.',MM),CASE WHEN (LEN(MM)-CHARINDEX('.',MM))<3 THEN
CAST(MM AS VARCHAR(200))+LEFT('00',2-(LEN(MM)-CHARINDEX('.',MM))) ELSE CAST(MM AS VARCHAR(200)) END FROM #TT1
/
MM WZ (无列名)
0.902 3 0.902
0.3 1 0.30
0.5 1 0.50
100.5 1 100.50
100.507 3 100.507
/
with t1(c1) as(
select convert(float, 0.902) union all
select 0.3 union all
select 0.5 union all
select 100.5)
select c1
, case
when c1*100- FLOOR(c1*100) =0 then convert(varchar,convert(decimal(18,2),c1))
else convert(varchar, c1)
end
from t1
with t1(danjia) as(
select 0.902 union all
select 0.3 union all
select 0.5 union all
select 100.5)
select convert(decimal(18,3),danjia) as 位数 from t1
/*
位数
0.902
0.300
0.500
100.500
*/