sql 如何去掉0

[mysmileh]

怎样在sql里面达到以下效果
如果是14.520000 就取14.52
如果是14.500000 就取14.5
如果是14.000000 还是取14

[sych888]

cast(id as float)

[abuying]

DECLARE @T TABLE(COL1 NUMERIC(19,3))
INSERT INTO @T
SELECT 12.000 UNION ALL
SELECT 10.300 UNION ALL
SELECT 9.005

SELECT COL1
,CAST(COL1 AS FLOAT)
,CASE WHEN CAST(COL1 AS VARCHAR(30)) LIKE '%.%' THEN
REVERSE(STUFF(REVERSE(CAST(COL1 AS VARCHAR(30))),1,PATINDEX('%[^0]%',REVERSE(CAST(COL1 AS VARCHAR(30))))-1,''))
ELSE
CAST(COL1 AS VARCHAR(30))
END
FROM @T

[冬雨在路上]

cast(num as float)

[rainzero]

转换为浮点数

[yy1987316]

原来浮点就可以啊，厉害

[快溜]

http://topic.csdn.net/u/20100507/08/96920763-51ca-4243-83ce-e17905791782.html?r=65236849

[fengyun142415]

转换为 decimal（10,2） 保留两位小数

[viqn7qdnt]

cast(列 as float)

[billpu]

转浮点

[chen_ya_ping]

declare @num float
set @num=14.520000

select CAST(@num as varchar(20))

set @num=14.500000

select CAST(@num as varchar(20))

set @num=14.000000

select CAST(@num as varchar(20))

[jiao3630]

[Quote=引用 4 楼 viqn7qdnt 的回复:]
SQL code

cast(列 as float)
[/Quote]
++

[-狙击手-]

cast(列 as float)

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id numeric(8,6))
insert into #tb
select 14.520000 union all
select 14.500000 union all
select 14.000000

select id=reverse(case when cast(reverse(cast(id as varchar(30)))as decimal(18,6)) <1 
			then right(reverse(cast(id as varchar(30))),len(reverse(cast(id as varchar(30))))-charindex('.',reverse(cast(id as varchar(30)))))
			else right(reverse(cast(id as varchar(30))),len(reverse(cast(id as varchar(30))))-patindex('%[^0]%',reverse(cast(id as varchar(30))))+1)
			end ) 
from #tb

id
------------------------------
14.52
14.5
14

(3 行受影响)

[rucypli]

select cast('14.520000' as float)

[st13985555264]

if object_id('tempdb.dbo.#a') is not null drop table #a
go
create table #a (id numeric(8,6))
insert into #a
select 14.520000 union all
select 14.500000 union all
select 14.000000

select cast(id as float) from #a

/*
14.52
14.5
14
*/

[luyuwei2008]

[Quote=引用 12 楼 sych888 的回复:]
cast(id as float)
[/Quote]
正解！！