如何去除小数位多余的0,有其他办法完美些解决吗?

[jiangchuandong]

在网上找了好几种方法都不是很理想
eg:

select cast('12.000' as float)

这样的结果是12.0


declare @s varchar(20)
set @s='12.120001'
select reverse(cast(cast(reverse(@s) as float)as varchar))


declare @s varchar(20)
set @s='12.120001'
select reverse(cast(reverse(@s) as float))

这样的结果有错是
120001

有其他办法完美些解决吗?

[jiangchuandong]

up

[dawugui]

我大概是看明白了.
把小数点后多余的0不显示出来.

看看我下面的结果对不对?

declare @cnt as decimal(18,6)
set @cnt = 19.312000
print reverse(substring(reverse(cast(@cnt as varchar)) , PATINDEX('%[1-9]%' , reverse(cast(@cnt as varchar))) , len(reverse(cast(@cnt as varchar)))))
set @cnt = 19.300000
print reverse(substring(reverse(cast(@cnt as varchar)) , PATINDEX('%[1-9]%' , reverse(cast(@cnt as varchar))) , len(reverse(cast(@cnt as varchar)))))

/*
19.312
19.3
*/

[dawugui]

--如果是190.000,有点问题.

declare @cnt as decimal(18,6)
set @cnt = 19.312000
print reverse(substring(reverse(cast(@cnt as varchar)) , PATINDEX('%[1-9]%' , reverse(cast(@cnt as varchar))) , len(reverse(cast(@cnt as varchar)))))
/*
19.312
*/

set @cnt = 19.300000
print reverse(substring(reverse(cast(@cnt as varchar)) , PATINDEX('%[1-9]%' , reverse(cast(@cnt as varchar))) , len(reverse(cast(@cnt as varchar)))))
/*
19.3
*/

set @cnt = 19.000000
print reverse(substring(reverse(cast(@cnt as varchar)) , PATINDEX('%[1-9]%' , reverse(cast(@cnt as varchar))) , len(reverse(cast(@cnt as varchar)))))
/*
19
*/

[jiangchuandong]

谢谢,看来高手确实多

[universee]

mark

[晓风残月0110]

mark

[jiangchuandong]

OK,测试中

[chuifengde]

我上面写的不行吗？

[zjcxc]

看楼主举的例子，都是把字符转换成float，再处理显示问题

如果转换成 float 是必须的，或者是楼主存储的数据本来就是 float 类型

则这个问题基本上无解， 因为 float 存储的本来就是非精度数据， 不能保证 100% 还原（有时候有很微小的误差），既然数据都不能 100% 还原到存储之前， 所以也无法得到楼主想要的结果

[zjcxc]

如果是字符串中存储的数字数据需要去掉小数中的尾部0， 则参考：


SELECT
col,
col_convert = CASE
WHEN CHARINDEX('.', col) = 0
THEN col
WHEN RIGHT(col, PATINDEX('%[^0]%', REVERSE(col))) LIKE '.%'
THEN LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)))
ELSE LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)) + 1)
END
FROM(
SELECT col = '100' UNION ALL
SELECT col = NULL UNION ALL
SELECT col = '.100' UNION ALL
SELECT col = '.100100' UNION ALL
SELECT col = '0.' UNION ALL
SELECT col = '0' UNION ALL
SELECT col = '100.1010' UNION ALL
SELECT col = '100.0000'
)A

-- 结果：
col col_convert
-------- -----------
100 100
NULL NULL
.100 .1
.100100 .1001
0. 0
0 0
100.1010 100.101
100.0000 100

(8 行受影响)

[wgzaaa]

要是numeric上面很多方法都是可行的

[jiangchuandong]

楼上试试 这个数值为numeric

2555.784000

[jiangchuandong]

select cast('2555.784000' as numeric(16,6))

[ruanchao]

顶

[jiangchuandong]

邹老师,如果我的类型是numeric,还有其他方法吗
如果是使用你的方法,需要先cast numeric 为 nvrchar类型

[Generics]

我的方法是没有问题的。

declare @s numeric(20,10)
set @s=2555.784000
SELECT CASE WHEN cast(@s as float)>cast(cast(@s as float) as INT)THEN cast(@s as float) ELSE cast(cast(@s as float) as int) END

[jiangchuandong]

up

[red_berries]

mark

[jiangchuandong]

好，我是试试看

[anison]

mark