如何在SQL中舍入小数点后4位

Question

我使用的是MS SQL服务器。我有一些varchar列需要转换为NUMERIC（19，4）。我正在尝试对字段进行强制转换和舍入，但在某些情况下，它似乎会截断到小数点后4位，而不是适当的舍入。举例来说：17.654593需要17.6546，而不是从最后的"93"。我正在使用以下内容更新整个字段。有没有办法给这个脚本添加一个"圆形"，以便它正确更新？

update table_name
SET price = case when isnull(price,'') <> ''
	    then CAST(CAST(price as FLOAT) as NUMERIC(19,4))
	    else 0
	    end

Answer 1

问题

1. 我建议不要选FLOAT。它本质上是一个不精确的数据类型，可能是舍入问题的原因。您应该能够直接转换为NUMERIC（19，4）
2. 由于这是一个VARCHAR列，所以一行可能包含一个值，这将导致您的语句失败。尝试在该字段中放入一些文本，然后运行修复程序——除非您在其他地方处理错误，否则它将失败
3. 如果将这个值推回到同一个VARCHAR列中，那么它无论如何都会隐式地转换回VARCHAR。它将被四舍五入到小数点后四位，但它仍然是VARCHAR。我认为这确实是你想要做的，但要考虑一下这是否是正确的解决方案
4. 另外，是否有充分的理由将NULL替换为0？这里只需要提醒一句，因为数字零和NULL的含义非常不同，如果你不小心，这可能会绊倒你或其他需要在以后使用数据的人

---

解决方案

SQL Server 2012或更高版本，解决舍入问题，解决到NUMERIC的不安全强制转换，并将NULL值保留为NULL：

update table_name
set price = ISNULL(TRY_CAST(price as NUMERIC(19,4)), price);

SQL Server 2012或更高版本，解决舍入问题，解决不安全的NUMERIC强制转换，将NULL值设置为0:

update table_name
set price = ISNULL(ISNULL(TRY_CAST(price as NUMERIC(19,4)), price), 0);

SQL Server 2008或更高版本，解决舍入问题，解决不安全的NUMERIC强制转换，将NULL值设置为0:

update table_name
set price = case
              when price is NULL
                then '0'
              when ISNUMERIC(price + 'e0') = 1
                then CAST(CAST(price as NUMERIC(19,4)) as VARCHAR)
              else price
            end

如果要阻止NULL转换为0，只需删除第一个when子句。