如何在T-SQL中自定义数字格式化？现有方案繁琐求更优解

问题现象 #

在SQL Server中使用FORMAT函数自定义数字掩码时，会出现忽略除小数掩码外的分组设置的情况（推测与SQL Server区域设置相关）：

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'#,##,#0.00' );

执行结果为123,456,789.12，而非预期的1,23,45,67,89.12。

尝试的解决方法 #

1. 转义分隔符尝试：尝试通过转义逗号和句号强制自定义掩码，结果仍不符合预期：

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'#\,##\,#0.00' )

得到结果12345,67,89.12。

2. 动态构建掩码尝试：编写脚本计算数字长度后构建对应掩码，虽能得到正确结果，但掩码过长会导致多余逗号：

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'##\,##\,##\,##\,#0.00' )

正确结果为1,23,45,67,89.12，但如果数字长度较短，会出现类似,,,,,123.45的多余逗号问题。

需求背景 #

出现该问题的核心需求源于：

• 不同国家/地区的数字格式差异（如1,234.56与1.234,56）
• 部分地区特殊的数字分组规则（如1,23,456.78）

现有方案的局限性 #

使用FORMAT函数的culture参数可实现部分地区的标准格式：

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'#,#0.00', N'en-US' ), FORMAT( @Number, N'#,#0.00', N'hi-IN' )

得到123,456,789.12（美式格式）和12,34,56,789.12（印度格式），但无法满足自定义分隔符（如用空格作为千分符、互换逗号与句号的角色）的需求。

优化思路 #

1. 结合FORMAT与字符串替换快速适配 #

对于只需修改分隔符的场景，可先用culture参数生成接近的格式，再通过REPLACE替换分隔符：
示例：将美式格式的逗号换成空格，句号换成逗号

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT 
  @Number,
  REPLACE(REPLACE(FORMAT(@Number, N'#,#0.00', N'en-US'), ',', ' '), '.', ',') AS CustomFormat

结果为123 456 789,12，快速实现分隔符自定义。

2. 自定义函数实现灵活分组 #

编写可复用的自定义函数，支持指定分组长度、整数分隔符、小数分隔符：
核心逻辑：

• 拆分数字的整数部分与小数部分
• 反转整数部分字符串，按指定长度拆分后拼接分隔符
• 再次反转得到正确顺序的整数部分，最后拼接小数部分
  示例函数简化版：

CREATE FUNCTION dbo.FormatCustomNumber(
  @Number numeric(20,5),
  @GroupLength int = 2,
  @IntSeparator nvarchar(5) = ',',
  @DecSeparator nvarchar(5) = '.'
)
RETURNS nvarchar(50)
AS
BEGIN
  DECLARE @IntPart nvarchar(30) = CAST(FLOOR(@Number) AS nvarchar(30));
  DECLARE @DecPart nvarchar(10) = RIGHT('00' + CAST((@Number - FLOOR(@Number)) * 100 AS nvarchar(10)), 2);
  
  -- 处理整数部分分组
  SET @IntPart = REVERSE(@IntPart);
  DECLARE @FormattedInt nvarchar(30) = '';
  WHILE LEN(@IntPart) > 0
  BEGIN
    SET @FormattedInt += @IntSeparator + LEFT(@IntPart, @GroupLength);
    SET @IntPart = RIGHT(@IntPart, LEN(@IntPart) - @GroupLength);
  END
  SET @FormattedInt = REVERSE(LTRIM(@FormattedInt, @IntSeparator));
  
  RETURN @FormattedInt + @DecSeparator + @DecPart;
END

调用示例：

SELECT dbo.FormatCustomNumber(123456789.123, 2, ',', '.') AS CustomResult;

得到结果1,23,45,67,89.12，且不会出现多余分隔符。

3. 利用STRING_SPLIT与窗口函数简化分组逻辑 #

对于SQL Server 2016+版本，可借助STRING_SPLIT和窗口函数实现更简洁的分组：

DECLARE @Number numeric(20,5) = 123456789.123;
DECLARE @IntPart nvarchar(30) = REVERSE(CAST(FLOOR(@Number) AS nvarchar(30)));
DECLARE @DecPart nvarchar(10) = RIGHT('00' + CAST((@Number - FLOOR(@Number)) * 100 AS nvarchar(10)), 2);

SELECT 
  @Number,
  REVERSE(STRING_AGG(SUBSTRING(@IntPart, (n-1)*2+1, 2), ',') WITHIN GROUP (ORDER BY n)) + '.' + @DecPart AS CustomFormat
FROM (
  SELECT TOP (CEILING(LEN(@IntPart)/2.0)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
  FROM sys.all_columns
) t;

该方法无需循环，通过生成序列拆分反转后的整数部分，再聚合拼接。

内容的提问来源于stack exchange，提问作者Storm