如何在 SQL Server 中使用 FORMAT() 函数对数字进行条件格式化

详细介绍在 SQL Server 中利用 FORMAT() 函数的分号节分隔符(Section Separators)特性,根据正数、负数和零的值对数字应用不同的自定义格式化规则。

发布于

在 SQL Server 中,FORMAT() 函数除了常用的日期格式化之外,还有一个极其实用的功能:对数值进行条件格式化(Conditional Formatting)。

由于 FORMAT() 内部依赖 .NET Framework 的格式化引擎,因此它原生支持 .NET 的自定义数字格式化规范。通过灵活运用格式字符串中的分号(;)作为节分隔符(Section Separator),我们可以让同一个表达式针对正数、负数以及零自动输出不同的格式。

[!NOTE] FORMAT() 函数适用于报表生成、数据导出和展示层格式化。由于其依赖 CLR 调用,在大数据量(如数百万行)的查询或关联条件中,建议优先在客户端或使用 CONVERT() 处理以获取最佳性能。

节分隔符 ; 的工作机制

在自定义数字格式字符串中,分号(;)充当条件节分隔符,最多可包含三个部分:

  • 单节(One Section):格式字符串不含分号。该格式无条件适用于所有数值(无论是正数、负数还是零)。这是最常见的用法。
  • 双节(Two Sections):格式字符串包含一个分号,分为两节。
    • 第一节(分号左侧):适用于正数和零。
    • 第二节(分号右侧):适用于负数。
    • 注:若负数经四舍五入后变为零,则按第一节规则格式化。
  • 三节(Three Sections):格式字符串包含两个分号,分为三节。
    • 第一节:适用于正数。
    • 第二节:适用于负数。
    • 第三节:适用于零。
    • 注:第二节可以留空(即两个分号连续出现 ;;),此时第一节适用于所有非零值(包括正数和负数),第三节单独适用于零。

此外,当显式指定了节分隔符时,默认情况下负数的负号(-)会被忽略;如果希望负数显示负号或括号,必须在格式定义中显式加入。


示例 1:单节格式(无条件格式化)

当格式字符串中没有分号时,所有数字都采用相同的格式输出:

SELECT 
  FORMAT(123, '0 (Number)') AS Positive,
  FORMAT(-123, '0 (Number)') AS Negative,
  FORMAT(0, '0 (Number)') AS Zero;

输出:

+--------------+---------------+------------+
| Positive     | Negative      | Zero       |
|--------------+---------------+------------|
| 123 (Number) | -123 (Number) | 0 (Number) |
+--------------+---------------+------------+

在此模式下,负数的减号会默认保留。


示例 2:双节格式(区分正数/零与负数)

使用一个分号将格式字符串分为两部分:

SELECT FORMAT(123, '0 (Positive or Zero); 0 (Negative)') AS Result;

输出:

+------------------------+
| Result                 |
|------------------------|
| 123 (Positive or Zero) |
+------------------------+

因为 123 是正数,系统匹配第一节格式。


示例 3:双节格式下对比正、负、零

传入不同的数值观察双节格式的实际渲染:

SELECT 
  FORMAT(123,  '0 (Positive or Zero); 0 (Negative)') AS Positive,
  FORMAT(-123, '0 (Positive or Zero); 0 (Negative)') AS Negative,
  FORMAT(0,    '0 (Positive or Zero); 0 (Negative)') AS Zero;

输出:

+------------------------+-----------------+----------------------+
| Positive               | Negative        | Zero                 |
|------------------------+-----------------+----------------------|
| 123 (Positive or Zero) |  123 (Negative) | 0 (Positive or Zero) |
+------------------------+-----------------+----------------------+

可以看到,-123 匹配第二节,输出时负号已被自动省略。


示例 4:双节格式下的四舍五入边界

当一个很小的负数经过格式化舍入后变为零时,系统会将其归入第一节(正数/零):

SELECT 
  FORMAT(0.1,  '0 (Positive or Zero); 0 (Negative)') AS Positive,
  FORMAT(-0.1, '0 (Positive or Zero); 0 (Negative)') AS Negative;

输出:

+----------------------+----------------------+
| Positive             | Negative             |
|----------------------+----------------------|
| 0 (Positive or Zero) | 0 (Positive or Zero) |
+----------------------+----------------------+

-0.1 四舍五入后为 0,因此同样使用了第一节格式。


示例 5:三节格式(正数、负数、零各自独立)

使用两个分号明确将正数、负数和零的格式分开:

DECLARE @fmt varchar(60) = '0 (Positive); 0 (Negative); 0 (Zero)';

SELECT 
  FORMAT(123,   @fmt) AS Positive,
  FORMAT(-123,  @fmt) AS Negative,
  FORMAT(0,     @fmt) AS Zero,
  FORMAT(0.123, @fmt) AS RoundedToZero;

输出:

+----------------+----------------+----------+-----------------+
| Positive       | Negative       | Zero     | RoundedToZero   |
|----------------+----------------+----------+-----------------|
| 123 (Positive) | 123 (Negative) | 0 (Zero) | 0 (Zero)        |
+----------------+----------------+----------+-----------------+

无论传入正数、负数还是零,都能准确命中各自预设的展示模板。


示例 6:三节格式中包含空节(;;)

如果第二节留空(两个分号相连),第一节规则将作用于所有非零数值:

SELECT 
  FORMAT(123,   '0 (Nonzero);; 0 (Zero)') AS Positive,
  FORMAT(-123,  '0 (Nonzero);; 0 (Zero)') AS Negative,
  FORMAT(0,     '0 (Nonzero);; 0 (Zero)') AS Zero,
  FORMAT(0.123, '0 (Nonzero);; 0 (Zero)') AS RoundedToZero;

输出:

+---------------+----------------+----------+-----------------+
| Positive      | Negative       | Zero     | RoundedToZero   |
|---------------+----------------+----------+-----------------|
| 123 (Nonzero) | -123 (Nonzero) | 0 (Zero) | 0 (Zero)        |
+---------------+----------------+----------+-----------------+

[!TIP] 当第二节留空时,负数的减号会被默认保留,因此输出为 -123 (Nonzero)。


示例 7:显式控制负数符号与会计括号

如果希望在使用节分隔符时负数依然显示减号或采用财务会计常用的括号表示法,只需在第二节中明确声明:

SELECT 
  FORMAT(-123, '$#,##0.00; -$#,##0.00; $0.00') AS WithMinus,
  FORMAT(-123, '$#,##0.00; ($#,##0.00); $0.00') AS AccountingFormat,
  FORMAT(0,    '$#,##0.00; ($#,##0.00); -')      AS ZeroDash;

输出:

+-------------+------------------+----------+
| WithMinus   | AccountingFormat | ZeroDash |
|-------------+------------------+----------|
| -$123.00    | ($123.00)        | -        |
+-------------+------------------+----------+

通过以上技巧,无需复杂的 CASE WHEN 分支逻辑,仅凭一个简洁的 FORMAT() 字符串即可优雅实现复杂的报表数值格式化。