MS SQL Server 2012 中 varchar 列中的值的舍入? [英] Rounding of a value in a varchar column in MS SQL Server 2012?

问题描述

我有一个奇怪的要求，需要在 SQL Server 2012 的 varchar 列中舍入一个值.我知道在 varchar 中保存数值是不好的做法.

即该列是一个 varchar 但包含一个表示数字的字符串.我有像 834.78330000000005 和 831.06669999999997 和 797.45000000000005 这样的字符串值，但我想将这些值更新为像 834.7833 和 831.0667 这样的字符串值.47 和 79p 太重要了，但 79p 不是很重要，但 79p 太重要了.

这似乎很接近了，有没有更好的选择?我应该使用round函数吗?

创建表 [临床].[AAAJFJunk]([呼叫长度] [NVARCHAR](50) NULL) 在 [主要]插入 [临床].[AAAJFJunk]([呼叫长度])值 (N'834.78330000000005'),(N'831.06669999999997'),(N'797.45000000000005')走更新临床.AAAJFJunkSET [呼叫长度] =CAST(CAST([Call Length] AS DECIMAL(11, 4)) AS VARCHAR);

解决方案

示例选择以检查建议值:

选择ISNULL([Call Length], '') = ''的情况然后 ''别的FORMAT(Cast ( CONVERT(numeric(16,4), CAST([Call Length] AS FLOAT)) as float),'########0.####')以 val123 结尾来自临床.AAAJFJunk

更新声明..

包括对空值的处理(即保持它们不变)

包括在源字符串值中使用 E 表示法处理科学值.例如6.6699999999999995E-2

包括删除数字右侧的尾随零

使用格式函数.

带有浮点数的 STR 函数会留下尾随零，所以我没有使用它.

将浮点数转换为字符串的 Convert 函数决定了它自己的小数位数，所以我也想避免这种情况！

UPDATE Clinical.AAAJFJunkSET [呼叫长度] =ISNULL([Call Length], '') = ''的情况然后 '' 否则FORMAT(Cast (CONVERT(numeric(16,4), CAST([Call Length] AS FLOAT)) as float),'########0.####') end

请注意格式说明符中的 0 具有特定含义.

请看:

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

I have a strange requirement to round a value in a varchar column in SQL Server 2012. I know it is bad practice to hold numeric values in a varchar.

i.e. the column is a varchar but holds a string representing a number. I have string values like 834.78330000000005 and 831.06669999999997 and 797.45000000000005 but I want to update these to string values like 834.7833 and 831.0667 and 797.45 (trimming the trailing zeros not too important but desirable).

This seems to be close, are there better options? Should I use round function?

CREATE TABLE [Clinical].[AAAJFJunk]
(
    [Call Length] [NVARCHAR](50) NULL
) ON [PRIMARY]

INSERT [Clinical].[AAAJFJunk] ([Call Length] ) 
VALUES (N'834.78330000000005'), 
       (N'831.06669999999997'),
       (N'797.45000000000005')
GO

UPDATE Clinical.AAAJFJunk
SET [Call Length] =
       CAST(CAST([Call Length] AS DECIMAL(11, 4)) AS VARCHAR);

解决方案

Example select to check proposed values:

select 
      case when ISNULL([Call Length], '') = '' 
      then '' 
      else 
 FORMAT(Cast ( CONVERT(numeric(16,4), CAST([Call Length] AS FLOAT)) as float),'########0.####')   
      end as    val123
    from Clinical.AAAJFJunk

Update statement ..

Includes handling of null value (i.e. leaves them untouched)

Includes handling of scientific values with E notation in source string values. e.g. 6.6699999999999995E-2

Includes removing trailing zero on right hand side of number

The Format function is used.

The STR function with a float leaves trailing zeros, so I didn't use that.

The Convert function with a float to a string determines it's own number of decimal places so I wanted to avoid that also !

UPDATE Clinical.AAAJFJunk
SET [Call Length] =
      case when ISNULL([Call Length], '') = '' 
      then '' else 
            FORMAT(Cast ( CONVERT(numeric(16,4), CAST([Call Length] AS FLOAT)) as float),'########0.####')   end

Please note the 0 in the format specifier has a specific meaning.

Please see:

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

这篇关于MS SQL Server 2012 中 varchar 列中的值的舍入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！