将有效数字作为 varchar 返回的 SQL 函数

这个问题需要一种非常特殊的数字格式，参考编号为columbia.edu 1和columbia.edu 2

回答的第一部分是验证2009年的现有功能(LimitSigFigs)(问题中的链接).这确实奏效了，但我相信还有一个更简单的类似功能(LimitSigFigsv1).我还认为保留将返回浮点数的函数是有益的(例如，用于对结果进行排序)，因此这两个函数中的任何一个都可以在后续的格式化中使用.

事实证明，这两个数字函数都完成了大部分"繁重任务"，基本上需要做的就是将输出强制转换为字符串，这可以通过Format函数来完成(nb:我try 过str()，但它的工作效果不如Format).但是，指定的特殊格式要求对需要特殊处理的小数添加一些额外的零.这是通过判断字符串的长度和小数点后第一个非零数位的位置来完成的.这将确定是否需要额外的零、需要多少个零，然后在需要时追加这些零.

最后(经过多次迭代)，格式化函数非常简单(这很好！)，并且有望执行得很好.注意，为了简单起见，我 Select 准备第二个函数，它将参数"传递"到现有的数值函数中.还请注意，用户定义的标量函数并不以其性能而闻名，但它们很方便.

下面是一个四舍五入到一个重要数字的简化函数，您可以使用这个函数或原始函数，但请注意，我还没有用这两个函数做过非常广泛的测试.

CREATE FUNCTION [dbo].[LimitSigFigsV1](@value FLOAT, @sigFigs INT) RETURNS FLOAT AS
BEGIN
  DECLARE @result FLOAT
  IF (@sigFigs < 1)
    SET @result = NULL
  ELSE
    SET @result = CASE WHEN @value = 0 THEN 0 
                       ELSE ROUND(@value, @sigFigs - 1 - FLOOR(LOG10(ABS(@value)))) 
                  END
  RETURN @result
END

• 将浮点数值和整数sigFigs作为输入.
• 返回一个四舍五入到指定有效位数的浮点数.
• 如果sigFigs输入小于1，则返回NULL.
• 如果输入值为0，则返回0.
• 否则，使用ROUND函数将该值四舍五入到指定的有效位数
• 然后返回四舍五入的值.

然后，通过此函数进行最终形成:

CREATE FUNCTION FormatSigFigsV1(@Number FLOAT, @Figures INT) RETURNS VARCHAR(200) AS 
BEGIN 
  DECLARE @Result VARCHAR(200); 
  DECLARE @Len TINYINT;
  DECLARE @Pos1 TINYINT;
  DECLARE @Dot TINYINT;
  
  SET @Result = FORMAT(dbo.LimitSigFigsv1(@Number, @Figures),'0.################') ; 
  SET @Len    = LEN(@Result) ;
  SET @Pos1   = PATINDEX('%[1-9]%', (@Result + '9')) - 1 ; 
  SET @Dot    = CHARINDEX('.', @Result) ;

  /* if number is decimal but length < @figures add zeros */
  IF (@Len - @Pos1) < @Figures AND @Dot > 0
  BEGIN 
    SET @Result = @Result + REPLICATE('0', @Figures - (@Len - @Pos1)) ; 
  END 
  
  /* if number is integer but length < @figures add dot & zeros */
  IF (@Len - @Pos1) < @Figures AND @Dot = 0
  BEGIN 
    SET @Result = @Result + '.' + REPLICATE('0', @Figures - (@Len - @Pos1)) ; 
  END 
  
  RETURN @Result; 
END

• 然后，该函数将@Result的值设置为调用LimitSigFigsv1函数的结果，传递参数@Number和@Figures.
• LimitSigFigsv1返回四舍五入到指定有效数字的浮点值
• 它使用Format函数通过‘0.#转换为字符串，以确保输出中包含尾随零.nb这是format()所允许的最大尾随零个数
• 如果结果的长度减go 第一个非零数位的位置小于@Figures，则该函数会在字符串的末尾添加必要数量的零.
• 然后，它返回格式化的结果字符串.

注意:已经进行了有限的测试，对于有效数字，使用下表，范围在1到5之间.(PS:我将初始测试集从(比方说)80125.01255更改为87125.112550，以减少对零的数量和位置的混淆).

这个答案的其余部分复制了小提琴站点的详细信息，希望这将允许您跟踪进行的测试和如何使用这些功能.

CREATE TABLE measurements (
  value decimal(24,8)
);

INSERT INTO measurements (value)
VALUES
  (87125.112550),  (8712.112550),  (871.112550),  (87.112550),  (8.112550),
  (0.111362),  (0.011362),  (0.001136),  (0.000113),  (0.000011),  (0.000001)
  ,(1000),(900),(100),(90),(10),(9), (0);

18 rows affected

CREATE FUNCTION [dbo].[LimitSigFigsV1](@value FLOAT, @sigFigs INT) RETURNS FLOAT AS
BEGIN
  DECLARE @result FLOAT
  IF (@sigFigs < 1)
    SET @result = NULL
  ELSE
    SET @result = CASE WHEN @value = 0 THEN 0 
                       ELSE ROUND(@value, @sigFigs - 1 - FLOOR(LOG10(ABS(@value)))) 
                  END
  RETURN @result
END

    CREATE FUNCTION FormatSigFigsV1(@Number FLOAT, @Figures INT) RETURNS VARCHAR(200) AS 
    BEGIN 
      DECLARE @Result VARCHAR(200); 
      DECLARE @Len TINYINT;
      DECLARE @Pos1 TINYINT;
      DECLARE @Dot TINYINT;
      
      SET @Result = FORMAT(dbo.LimitSigFigsv1(@Number, @Figures),'0.################') ; 
      SET @Len    = LEN(@Result) ;
      SET @Pos1   = PATINDEX('%[1-9]%', (@Result + '9')) - 1 ; 
      SET @Dot    = CHARINDEX('.', @Result) ;

      /* if number is decimal but length < @figures add zeros */
      IF (@Len - @Pos1) < @Figures AND @Dot > 0
      BEGIN 
        SET @Result = @Result + REPLICATE('0', @Figures - (@Len - @Pos1)) ; 
      END 
      
      /* if number is integer but length < @figures add dot & zeros */
      IF (@Len - @Pos1) < @Figures AND @Dot = 0
      BEGIN 
        SET @Result = @Result + '.' + REPLICATE('0', @Figures - (@Len - @Pos1)) ; 
      END 
      
      RETURN @Result; 
    END

/* alternate logic */
CREATE FUNCTION FormatSigFigsV2(@Number FLOAT, @Figures INT) RETURNS VARCHAR(200) AS 
BEGIN 
  DECLARE @Result VARCHAR(200); 
  SET @Result = FORMAT(dbo.LimitSigFigsv1(@Number, @Figures),'0.################'); 

  /* Count the number of significant digits */
  DECLARE @SigFigs INT = 0;
  DECLARE @i INT = 1;
  WHILE @i <= LEN(@Result) AND @SigFigs < @Figures
  BEGIN
    IF SUBSTRING(@Result, @i, 1) BETWEEN '1' AND '9'
      SET @SigFigs = @SigFigs + 1;
    SET @i = @i + 1;
  END

  /* Add trailing zeros if necessary */
  IF CHARINDEX('.', @Result) > 0 AND @SigFigs < @Figures
    SET @Result = @Result + REPLICATE('0', @Figures - @SigFigs);
  ELSE IF CHARINDEX('.', @Result) = 0 AND @SigFigs < @Figures
    SET @Result = @Result + '.' + REPLICATE('0', @Figures - @SigFigs);

  RETURN @Result; 
END

SELECT 
    value AS Measured,
    dbo.FormatSigFigsV1(value, 1) AS Sig_1,
    dbo.FormatSigFigsV1(value, 2) AS Sig_2,
    dbo.FormatSigFigsV1(value, 3) AS Sig_3,
    dbo.FormatSigFigsV1(value, 4) AS Sig_4,
    dbo.FormatSigFigsV1(value, 5) AS Sig_5,
    dbo.FormatSigFigsV2(value, 1) AS Sig_1a,
    dbo.FormatSigFigsV2(value, 2) AS Sig_2a,
    dbo.FormatSigFigsV2(value, 3) AS Sig_3a,
    dbo.FormatSigFigsV2(value, 4) AS Sig_4a,
    dbo.FormatSigFigsV2(value, 5) AS Sig_5a
FROM measurements;

/*
+---------------+-----------+------------+------------+-------------+--------------+
|   Measured    | 1 Sig Fig | 2 Sig Figs | 3 Sig Figs | 4 Sig Figs  |  5 Sig Figs  |
+---------------+-----------+------------+------------+-------------+--------------+
| 87125.01255   | 90000     | 87000      | 87100      | 87130       | 87125        |
| 8712.01255    | 9000      | 8700       | 8710       | 8712        | 8712.0       |
| 871.012555    | 900       | 870        | 871        | 871.0       | 871.01       |
| 87.01255      | 90        | 87         | 87.0       | 87.01       | 87.013       |
| 8.01255       | 8         | 8.0        | 8.01       | 8.013       | 8.0126       |
| 0.100362      | 0.1       | 0.10       | 0.100      | 0.1004      | 0.10036      |
| 0.010362      | 0.01      | 0.010      | 0.0103     | 0.01036     | 0.010362     |
| 0.001036      | 0.001     | 0.0010     | 0.00104    | 0.001036    | 0.0010360    |
| 0.000103      | 0.0001    | 0.00010    | 0.000103   | 0.0001030   | 0.00010300   |
| 0.000010      | 0.00001   | 0.000010   | 0.0000100  | 0.00001000  | 0.000010000  |
| 0.000001      | 0.000001  | 0.0000010  | 0.00000100 | 0.000001000 | 0.0000010000 |
+---------------+-----------+------------+------------+-------------+--------------+
*/

fiddle个