如何在JSON对象中插入非科学计数法的浮点数（SQL Server）

需要向JSON对象中添加浮点/十进制数值属性，但现有方法无法得到理想格式：

• 使用FLOAT类型时，生成的JSON会采用科学计数法：

DECLARE @json VARCHAR(MAX) = '{}'
DECLARE @floatInStringForm VARCHAR(20) = '6780.75'

SET @json = JSON_MODIFY(@json, '$.TestFloat', CAST(@floatInStringForm AS FLOAT))
SELECT @json

期望输出

{"TestFloat":6780.75}

实际输出

{"TestFloat":6.780750000000000e+003}

• 使用DECIMAL类型时，会生成多余的末尾零，缩小精度又无法兼容更多小数位的数值：

DECLARE @json VARCHAR(MAX) = '{}'
DECLARE @floatInStringForm VARCHAR(20) = '6780.75'

SET @json = JSON_MODIFY(@json, '$.TestFloat', CAST('6780.75' AS DECIMAL(20, 10)))
SELECT @json

实际输出

{"TestFloat":6780.7500000000}

使用版本：Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 6 2023 08:32:07 Copyright (C) 2022 Microsoft Corporation

可以通过先清理输入字符串的末尾零和多余小数点，再结合JSON函数插入数值的方式，既保留正确格式又兼容多小数位：

实现代码 #

DECLARE @json VARCHAR(MAX) = '{}'
DECLARE @floatInStringForm VARCHAR(20) = '6780.75'

-- 步骤1：清理字符串，去除末尾的零和多余的小数点
SET @floatInStringForm = CASE 
    WHEN CHARINDEX('.', @floatInStringForm) > 0 THEN
        -- 先把零替换成空格，去掉末尾空格后再换回零
        REPLACE(RTRIM(REPLACE(@floatInStringForm, '0', ' ')), ' ', '0')
    ELSE
        @floatInStringForm
END
-- 处理清理后末尾是小数点的情况（比如输入是"123."）
SET @floatInStringForm = CASE 
    WHEN RIGHT(@floatInStringForm, 1) = '.' THEN
        LEFT(@floatInStringForm, LEN(@floatInStringForm)-1)
    ELSE
        @floatInStringForm
END

-- 步骤2：通过JSON_VALUE将清理后的字符串转为JSON数值，再插入到目标JSON
SET @json = JSON_MODIFY(@json, '$.TestFloat', JSON_VALUE('{"val":' + @floatInStringForm + '}', '$.val'))
SELECT @json

效果说明 #

• 输入'6780.75'时，输出{"TestFloat":6780.75}
• 输入'123.4500'时，输出{"TestFloat":123.45}
• 输入'9876'时，输出{"TestFloat":9876}
• 输入'12.345000'时，输出{"TestFloat":12.345}

该方法既避免了科学计数法，又不会生成多余的末尾零，同时能灵活处理任意合法的小数位数值。

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