我有一个列
DECIMAL(9,6)
，即它支持999,123456这样的值。
但是当我插入123,4567这样的数据时，就变成了123,456700。
如何删除这些零？
在SQL Server中删除小数的尾部零点
101
人关注
2
个评论
dangalg
：
请将正确答案改为@Andomar的答案
@dangalg:我坚信这确实应该在表现层完成。这就是为什么我从未改变已接受的答案。但是经过进一步的考虑，我认为我应该接受社区明确指出的最佳答案。
abatishchev
发布于
2010-05-30
23
个回答
Andomar
发布于
2017-06-02
已采纳
0
人赞同
一个
decimal(9,6)
，在逗号的右边存储6位数字。 是否显示尾数零是一个格式化的决定，通常在客户端实现。
但是由于SSMS格式化
float
，没有尾部的零，你可以通过把
decimal
转换成
float
来删除尾部的零。
select
cast(123.4567 as DECIMAL(9,6))
, cast(cast(123.4567 as DECIMAL(9,6)) as float)
123.456700 123,4567
(我的小数点分隔符是一个逗号，然而SSMS用一个点来格式化小数点。 显然，这是一个已知的问题）。
+1 我以为转换为浮点数会给结果带来一些不精确性，但看起来工作得非常好。
这种方法的一个缺点是，如果你以 "2.0 "开头，它将变成 "2"。这对提问的人来说可能没问题，但我需要在小数点后保留一个零，而不保留其他尾部的零。@用户1959416的回答解决了这个问题。
HLGEM
：
另外，一般来说，float是存储数字的一个非常糟糕的选择。你会得到四舍五入的错误，因为它不是一个精确的类型。永远不要使用float。
关于浮点数的格式化没有尾部零的评论非常有用
但我认为小数的比例和精度可能超过浮点数，因此可能会出现（超过17位的重要数字）这个答案不成立的情况，这种想法是否正确？
robert4
发布于
2017-06-02
0
人赞同
你可以使用
FORMAT()
函数（SqlAzure和Sql Server 2012+）。
SELECT FORMAT(CAST(15.12 AS DECIMAL(9,6)), 'g18') -- '15.12'
SELECT FORMAT(CAST(0.0001575 AS DECIMAL(9,6)), 'g10') -- '0.000158'
SELECT FORMAT(CAST(2.0 AS DECIMAL(9,6)), 'g15') -- '2'
在与FLOAT（或REAL）一起使用时要小心：不要使用g17 或更大（或与REAL一起使用g8 或更大），因为机器表示的有限精度会导致不想要的效果。
SELECT FORMAT(CAST(15.12 AS FLOAT), 'g17') -- '15.119999999999999'
SELECT FORMAT(CAST(0.9 AS REAL), 'g8') -- '0.89999998'
SELECT FORMAT(CAST(0.9 AS REAL), 'g7') -- '0.9'
此外，请注意，根据文档，。
此外，请注意，根据文档： FORMAT依赖于.NET框架的公共语言运行时（CLR）的存在。
Runtime (CLR)。这个函数将不会被遥控，因为它依赖于
因为它依赖于CLR的存在。遥控一个需要CLR的函数
会在远程服务器上导致错误。
在SqlAzure中也适用。
对于我的目的，我发现一个格式化字符串g8将我的数字格式化为 "1e-08"，这并不是我想要的。不过，这个答案确实让我找到了一个可以使用的答案
Caius Jard
发布于
2017-06-02
0
人赞同
我不愿意投给float，因为我的小数有可能比float能表示的数字多。
FORMAT
当与标准的.net格式字符串'g8'一起使用时，在小数非常小的情况下（例如1e-08）返回科学符号，这也是不合适的。
使用一个自定义的格式字符串
（https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings）
允许我实现我想要的东西。
DECLARE @n DECIMAL(9,6) =1.23;
SELECT @n
--> 1.230000
SELECT FORMAT(@n, '0.######')
--> 1.23
如果你想让你的数字至少有一个尾部的零，所以2.0不会变成2，使用一个格式字符串，如0.0#####
小数点是本地化的，所以使用逗号作为小数点分隔符的文化会遇到逗号的输出，.
当然，这是让数据层进行格式化的不可取的做法（但在我的案例中，没有其他层；用户实际上是在运行一个存储过程，并将结果放在电子邮件中 :/ )
Tor
：
是的，这是唯一的解决方案，允许完全控制小数点分隔符右边的数字，同时避免科学符号。这个解决方案的唯一问题是，FORMAT真的很（！）慢。（还是在SQL2019上）。
Bat_Programmer
发布于
2017-06-02
0
人赞同
SELECT CONVERT(DOUBLE PRECISION, [ColumnName])
SQL Server 2008及以上版本
当数字是 "123.10705000000 "时会发生什么？我试着用SELECT CONVERT(DOUBLE PRECISION,123.10705000000)，但它给我的答案是 "123.107"，而我想要 "123.10705 "作为输出？有什么办法吗？我不想使用CHARINDEX。
user1959416
发布于
2017-06-02
0
人赞同
SELECT REVERSE(ROUND(REVERSE(2.5500),1))
这个方法很好，如果只有一个零，它会留下一个尾数零。所以2.5500返回2.55，2.000返回2.0而不是2。当你对车辆的发动机尺寸进行格式化时，这个方法非常好...
gotqn
：
@MasonG.Zhwiti 我怀疑这对一些小数点后有更多数字的小数会起作用，比如说232.33220003200 :- )
@gotqn 说得好，这肯定是失败的。然而，对于我们的特定用例（格式化汽车中的发动机尺寸），它完美地工作。）
正如@Gotqn所说......当数字很长时，它就会出现错误。
这对像0.56000这样的数字并不完全有效，它将产生56。有趣的是
Todd
发布于
2017-06-02
0
人赞同
试试这个:
SELECT REPLACE(TRIM(REPLACE(20.5500, "0", " ")), " ", "0")
给予20.55
REPLACE(TRIM(REPLACE(20.00, "0"," ")), " ", "0")留给你一个尾巴。 => "20"。
它完全适用于我的情况，似乎是最简单的解决方案。拇指和投票!
wilson
：
很好的解决方案，不需要转换为浮动值！有一个小问题，
0.000
，变成了
.
。这里是修正
SELECT REPLACE(RTRIM(REPLACE(20.5500, "0", " ")), " ", "0")
，只修剪尾部的零。
这是为了摆脱圆点。
SELECT REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(25.00, '0', ' ')), ' ', '0'),'.',' ')),' ','.')
Adge Cutler
发布于
2017-06-02
0
人赞同
我有一个类似的问题，但也被要求在没有小数点的地方删除小数点，这是我的解决方案，它将小数点分割成其组成部分，并根据分数组成部分的长度（不使用CASE），从小数点字符串中提取的字符数。为了使事情更加有趣，我的数字被存储为不带小数的浮点数。
DECLARE @MyNum FLOAT
SET @MyNum = 700000
SELECT CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),2) AS VARCHAR(10))
+ SUBSTRING('.',1,LEN(REPLACE(RTRIM(REPLACE(CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),1) AS VARCHAR(2)),'0',' ')),' ','0')))
+ REPLACE(RTRIM(REPLACE(CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),1) AS VARCHAR(2)),'0',' ')),' ','0')
我知道这个结果是很痛苦的，但在上面的答案的帮助下，我达到了目的。
Mahmoud Moravej
发布于
2017-06-02
0
人赞同
最好的方法是在转换前不转换为
FLOAT
或
MONEY
，因为有可能失去精度。因此，安全的方法可以是这样的。
CREATE FUNCTION [dbo].[fn_ConvertToString]
@value sql_variant
RETURNS varchar(max)
BEGIN
declare @x varchar(max)
set @x= reverse(replace(ltrim(reverse(replace(convert(varchar(max) , @value),'0',' '))),' ',0))
--remove "unneeded "dot" if any
set @x = Replace(RTRIM(Replace(@x,'.',' ')),' ' ,'.')
return @x
其中@value可以是任何十进制(x,y)
@abatishchev ，fn_是sql函数的首选前缀，前缀用于标准编码和命名惯例以及最佳实践，我们可以定制我们的前缀，但对于最佳实践，我们使用
sp_
存储过程，
fn_
函数，
tbl
表等等。这不是一个要求，但这是组织我们数据库的最佳实践。
@japongskie：对不起，但不是。没有必要使用前缀，完全没有必要。这实际上是最糟糕的做法。见
msdn.microsoft.com/en-us/library/dd172115(v=vs.100).aspx
sqlperformance.com/2012/10/t-sql-queries/sp_prefix
dba.stackexchange.com/q/25348/3186
等等。
@abatishchev ，哦，我明白了......所以我不会再遵循学校的教学了......哈哈，LOL，因为你的链接来自mdsn，感谢这个链接，我现在会改变我的最佳做法... :)
Kazeem Muritala
发布于
2017-06-02
0
人赞同
我有一个类似的问题，需要修剪数字的尾部零，如
xx0000,x00000,xxx000
我使用了。
select LEFT(code,LEN(code)+1 - PATINDEX('%[1-Z]%',REVERSE(code))) from Tablename
代码是带有要修剪的数字的字段的名称。希望这能帮助其他人。
当你使用的SQL Server版本老到不能使用SQL Server中的TRIM或FORMAT内置函数时，这个评论很有效。
我确实发现了这个答案的一个问题。 如果'代码'字段中的数值是'10'之类的，那么它将返回'1'。 如果数字是'10.00'，我认为它也忽略了小数点。
SQLian
发布于
2017-06-02
0
人赞同
另一个选择...
我不知道这样做的效率如何，但它似乎是有效的，而且不通过浮动。
select replace(rtrim(replace(
replace(rtrim(replace(cast(@value as varchar(40)), '0', ' ')), ' ', '0')
, '.', ' ')), ' ', '.')
中间一行去掉后面的空格，外面两行去掉点，如果没有小数位的话
Ali
发布于
2017-06-02
0
人赞同
我需要去掉小数上的尾部零，这样我就可以输出一个只有
前导
零的一定长度的字符串了
(例如，我需要输出14个字符，这样142.023400就会变成0000142.0234）。
我使用
parsename
,
reverse
和
cast
as int
来删除尾部的零。
SELECT
PARSENAME(2.5500,2)
+ '.'
+ REVERSE(CAST(REVERSE(PARSENAME(2.5500,1)) as int))
(为了得到我的前导零，我可以根据上面的长度复制出正确的零数，并将其连接到上面的前面）。
我希望这能帮助别人。
Ishtiaq
发布于
2017-06-02
0
人赞同
在TSQL中删除前导和尾部的零是可能的。
使用STR TSQL函数将其转换为字符串，如果不是字符串，那么
去除前导零和尾随零
SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount
更多信息请见论坛。
Chris B
：
有点难看，但这个版本杀死了剩下的'.'。
REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(X,'0',' ')),' ','0'),'.',' ')),' ','.')
Muflix
：
请注意，如果数字不是小数，它也会修剪零。CHARINDEX('.',@Number) !=1将测试这个问题。
Muflix
：
我之前的小数点检查是错误的。下面是更好的办法。选择Len(@Test) - Len(Replace(@Test, 'a', ''))作为NumberOfCharacters 解释：
tinyurl.com/o4fc8g7
和
tinyurl.com/kgzkuqk
MrNazgul
发布于
2017-06-02
0
人赞同
这样呢？ 假设进入你的函数的数据是@thisData。
BEGIN
DECLARE @thisText VARCHAR(255)
SET @thisText = REPLACE(RTRIM(REPLACE(@thisData, '0', ' ')), ' ', '0')
IF SUBSTRING(@thisText, LEN(@thisText), 1) = '.'
RETURN STUFF(@thisText, LEN(@thisText), 1, '')
RETURN @thisText
beloblotskiy
发布于
2017-06-02
0
人赞同
case when left(replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0', ' '))), ' ', '0'), 1) = '.'
then '0'
else ''
end +
replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0', ' '))), ' ', '0') +
case when right(replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0', ' '))), ' ', '0'), 1) = '.'
then '0'
else ''
mtk
：
这不是也要替换数字之间的0（零）吗？不认为替换只是在两端起作用......
Abhi
发布于
2017-06-02
0
人赞同
我知道这是个老帖子，但我想提供我想出的SQL语言。
DECLARE @value DECIMAL(23,3)
set @value = 1.2000
select @value original_val,
SUBSTRING( CAST( @value as VARCHAR(100)),
PATINDEX('%.%',CAST(@value as VARCHAR(100)))
+ CASE WHEN ROUND(
REVERSE( SUBSTRING( CAST(@value as VARCHAR(100)),
PATINDEX('%.%',CAST(@value as VARCHAR(100)))+1,
LEN(CAST(@value as VARCHAR(100)))
,1) > 0 THEN
+ REVERSE(ROUND(REVERSE(SUBSTRING( CAST(@value as VARCHAR(100)),
PATINDEX('%.%',CAST(@value as VARCHAR(100)))+1,
LEN(CAST(@value as VARCHAR(100)))
),1))
ELSE '' END AS modified_val
Vishal Kiri
发布于
2017-06-02
0
人赞同
试试这个。
select CAST(123.456700 as float),cast(cast(123.4567 as DECIMAL(9,6)) as float)
Brad Raiche
发布于
2017-06-02
0
人赞同
最简单的方法是将该值 CAST 为 FLOAT，然后转为字符串数据类型。
CAST(CAST(123.456000 AS FLOAT) AS VARCHAR(100))
hamish
发布于
2017-06-02
0
人赞同
在上述所有的答案中，我只找到了两个真正有效的答案。不要转换为浮点数，这里证明如果你这样做，3.175是不行的。
SELECT round(cast(3.175 as float), 2) as roundingcheck,
CASE WHEN
round(cast(3.175 as float), 2) = 3.18 THEN 'PASSED' ELSE 'FAILED' END
as roundecheck,
CAST(round(TRY_CONVERT(DECIMAL(28,2), cast(3.175 as
float)), 2) as nvarchar(max)) as roundconvert,
round(CAST(3.175 as DECIMAL(18,10)), 4) as check1,
cast(CAST(round(CAST(3.175 as DECIMAL(18,10)), 4) as decimal(18,2)) as nvarchar(max)) as
roundconvert2,
cast(CAST(CAST(3.175 as DECIMAL(18,10)) as decimal(18,2)) as nvarchar(max)) as roundconvert3,
cast(CAST(CAST(3.149 as DECIMAL(18,10)) as decimal(18,1)) as nvarchar(max)) as roundconvert4,
cast(FORMAT(round(CAST(3.175 as
DECIMAL(18,10)), 2), '0.######') as nvarchar(max)) as roundconvert5
3.17 FAILED 3.17 3.1750000000 3.18 3.18 3.1 3.18
有效的答案。
如果你想同时四舍五入到小数点后2位，用这个。
cast(CAST(CAST(3.175 as DECIMAL(18,10)) as decimal(18,2)) as nvarchar(max)) as roundconvert3,
如果你不知道会有多少位小数，你可以使用这个，如果需要的话，可以和四舍五入一起使用。
cast(FORMAT(round(CAST(3.175 as DECIMAL(18,10)), 2), '0.######') as nvarchar(max))
Vipin J S
发布于
2017-06-02
0
人赞同
试试这个。
select Cast( Cast( (ROUND( 35.457514 , 2) *100) as Int) as float ) /100
在这里我更喜欢@user1959416的答案，因为它没有改变数值。例如，从2.5550开始，你的方法结果是2.56，而他们的方法则返回2.555。
Alan Schofield
发布于
2017-06-02
0
人赞同
我知道这个话题已经很老了，但是对于那些没有使用SQL Server 2012或以上版本的人，或者由于某种原因不能使用FORMAT函数的人来说，下面的方法是可行的。
另外，如果数字小于1（例如0.01230000），很多解决方案都不起作用。
请注意，下面的方法对负数不起作用。
DECLARE @num decimal(28,14) = 10.012345000
SELECT PARSENAME(@num,2) + REPLACE(RTRIM(LTRIM(REPLACE(@num-PARSENAME(@num,2),'0',' '))),' ','0')
set @num = 0.0123450000
SELECT PARSENAME(@num,2) + REPLACE(RTRIM(LTRIM(REPLACE(@num-PARSENAME(@num,2),'0',' '))),' ','0')
分别返回10.012345和0.012345。
kierzo
发布于
2017-06-02
0
人赞同
试试这个。
select isnull(cast(floor(replace(rtrim(ltrim('999,999.0000')),',','')) as int),0)
这个返回
999999
，OP要求删除尾部的零。
Kobus
发布于
2017-06-02
0
人赞同
DECIMAL(9,6)列会转换为浮点数而不损失精度，所以CAST(...AS float)会做这个动作。
@HLGEM：说float是存储数字的一个糟糕的选择，"永远不要使用float "是不正确的--你只需要知道你的数字，例如，温度测量将很好地作为float。