如何在SQL Server 2008 R2中将十进制转换为科学

I have a table named Product. The table looks like this:

我有一个名为Product的表。该表如下所示:

ID   Product           Volume
1      xyz       4654.000000000000000
2      abc       121.000000000000000

I want to represent Volume in a scientific notation. Volume is of datatype decimal(20,8).

我想用科学记数法表示Volume。卷的数据类型为十进制(20,8)。

How can I do this conversion?

我该怎么做这个转换?

2 个解决方案

#1

Normally, the formatting and presentation of data should be done by UI, on the client side, but if you insist...

通常,数据的格式和表示应该在客户端通过UI完成,但如果你坚持......

DECLARE @t decimal(20,8) = 4654.000000000000000;
SELECT @t, CONVERT(nvarchar(32), CAST(@t as float), 2)

result

4654.00000000   4.654000000000000e+003

First CAST decimal to float (please note, it may loose precision), then CONVERT float to nvarchar using style 2:

第一个CAST十进制浮点数(请注意,它可能会松散精度),然后使用样式2 CONVERT float到nvarchar:

Always 16 digits. Always use in scientific notation.

总是16位数。始终使用科学记数法。

Note

float type has only 15 digits of precision, so it can't store your decimal(20,8) without loss of precision.

float类型只有15位精度,所以它不能存储你的小数(20,8)而不会损失精度。

#2

declare @t decimal(20,8)=132423423421.00000000
print  cast(@t as float)

you can cast volume field to float in select statement and then you will get scientific notation of it as shown above.

您可以在select语句中将volume字段转换为float,然后您将获得它的科学记数,如上所示。