如何在SQL中将DECIMAL类型金额列转换为印度卢比逗号分隔格式？

数据老陈

2026-7-8

SQL中将DECIMAL金额转换为印度卢比逗号分隔格式的解决方案

需求明确：将DECIMAL类型的金额列转换成印度卢比特有的逗号分隔格式（从右往左，先3位分组，之后每2位分组），例如输入50000000.00，期望输出为5,00,00,000。

方案一：利用FORMAT函数（推荐，SQL Server 2012及以上版本）

SQL Server的FORMAT函数支持指定区域设置，直接生成印度格式的数字字符串：

-- 保留小数位，输出5,00,00,000.00
SELECT FORMAT(your_amount_column, 'N', 'en-IN') AS formatted_amount
FROM your_table;

-- 去掉小数位，输出5,00,00,000
SELECT REPLACE(FORMAT(your_amount_column, 'N', 'en-IN'), '.00', '') AS formatted_amount
FROM your_table;

-- 或直接指定格式字符串，无需替换操作
SELECT FORMAT(your_amount_column, '#,##,##,###', 'en-IN') AS formatted_amount
FROM your_table;

方案二：针对旧版SQL Server（无FORMAT函数）

如果你的SQL Server版本低于2012，无法使用FORMAT，可以通过字符串拼接手动处理分组逻辑：

SELECT 
    CASE 
        WHEN LEN(CAST(FLOOR(your_amount_column) AS VARCHAR)) <= 3 THEN CAST(FLOOR(your_amount_column) AS VARCHAR)
        WHEN LEN(CAST(FLOOR(your_amount_column) AS VARCHAR)) <= 5 THEN LEFT(CAST(FLOOR(your_amount_column) AS VARCHAR), LEN(CAST(FLOOR(your_amount_column) AS VARCHAR))-3) + ',' + RIGHT(CAST(FLOOR(your_amount_column) AS VARCHAR), 3)
        ELSE LEFT(CAST(FLOOR(your_amount_column) AS VARCHAR), LEN(CAST(FLOOR(your_amount_column) AS VARCHAR))-5) + ',' + SUBSTRING(CAST(FLOOR(your_amount_column) AS VARCHAR), LEN(CAST(FLOOR(your_amount_column) AS VARCHAR))-4, 2) + ',' + RIGHT(CAST(FLOOR(your_amount_column) AS VARCHAR), 3)
    END AS formatted_amount
FROM your_table;