SQL Server使用Replace命令转换JSON multilinestring坐标格式的方法

原有代码问题说明 #

你写的代码只是做了全局的字符替换，会把所有[替换成(、]替换成)，同时删掉所有空格，最终输出结果是(((1,1),(2,2),(3,3),(4,4)))，完全不符合要求，核心原因是没有处理JSON的层级结构，也没有完成坐标点内逗号转空格、每两个坐标点打包成一组的需求。

正确实现方案 #

推荐使用SQL Server自带的JSON解析函数OPENJSON处理，比硬字符替换更稳定，不会因为JSON内的空格、换行差异出现异常：

DECLARE @r1 VARCHAR(MAX) = '[  [  [ 1, 1 ], [ 2, 2 ],  [ 3, 3 ],[ 4, 4 ]  ]  ]'
DECLARE @r2 VARCHAR(MAX)

-- 解析JSON提取所有坐标点并编号
;WITH AllPoints AS (
    SELECT 
        -- 将[x,y]格式转为x y
        CONCAT(JSON_VALUE(point.value, '$[0]'), ' ', JSON_VALUE(point.value, '$[1]')) AS point_str,
        ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS rn
    FROM OPENJSON(@r1, '$[0]') point
)
-- 每两个相邻点为一组拼接成目标格式
SELECT @r2 = STRING_AGG(CONCAT('(', p1.point_str, ', ', p2.point_str, ')'), ', ')
FROM AllPoints p1
INNER JOIN AllPoints p2 ON p1.rn = p2.rn - 1
WHERE p1.rn % 2 = 1

SELECT @r2 AS 转换后r2格式

运行后输出结果为(1 1, 2 2), (3 3, 4 4)，和要求完全一致。

低版本SQL Server兼容方案 #

如果你的SQL Server版本不支持STRING_AGG（2016及更早版本），可以用FOR XML PATH方式实现拼接：

DECLARE @r1 VARCHAR(MAX) = '[  [  [ 1, 1 ], [ 2, 2 ],  [ 3, 3 ],[ 4, 4 ]  ]  ]'
DECLARE @r2 VARCHAR(MAX) = ''

;WITH AllPoints AS (
    SELECT 
        CONCAT(JSON_VALUE(point.value, '$[0]'), ' ', JSON_VALUE(point.value, '$[1]')) AS point_str,
        ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS rn
    FROM OPENJSON(@r1, '$[0]') point
),
PointGroups AS (
    SELECT CONCAT('(', p1.point_str, ', ', p2.point_str, ')') AS group_str
    FROM AllPoints p1
    INNER JOIN AllPoints p2 ON p1.rn = p2.rn - 1
    WHERE p1.rn % 2 = 1
)
SELECT @r2 = @r2 + CASE WHEN @r2 <> '' THEN ', ' ELSE '' END + group_str
FROM PointGroups

SELECT @r2 AS 转换后r2格式

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