如何将任意长度SQL字符串按$分隔拆分后插入descr和meter两列

逻辑说明 #

传入的字符串以$为分隔符，按「描述内容、数值」的规律两两交替排列，拆分后将相邻的两个值分别对应descr和meter字段插入表即可。以下提供两种兼容不同SQL Server版本的实现： #

实现1：SQL Server 2022及以上版本（原生函数支持） #

利用STRING_SPLIT自带的序号参数直接定位元素，代码最简洁：

declare @string varchar(max) 
set @string = 'desc1$100$desc2$200'

declare @table table
        (descr varchar(max),
            meter int
        )

-- 插入逻辑
;WITH SplitItems AS (
    -- 第三个参数1表示开启序号返回，ordinal为拆分后元素的顺序编号
    SELECT value, ordinal
    FROM STRING_SPLIT(@string, '$', 1)
)
INSERT INTO @table (descr, meter)
SELECT 
    s1.value AS descr,
    CAST(s2.value AS INT) AS meter
FROM SplitItems s1
INNER JOIN SplitItems s2 
    ON s1.ordinal = s2.ordinal - 1
WHERE s1.ordinal % 2 = 1

-- 测试查询
SELECT * FROM  @table

实现2：兼容所有SQL Server版本（递归CTE实现） #

如果使用2022之前的版本，没有STRING_SPLIT的序号参数，可以用递归CTE自行生成带顺序号的拆分结果：

declare @string varchar(max) 
set @string = 'desc1$100$desc2$200'

declare @table table
        (descr varchar(max),
            meter int
        )

-- 插入逻辑
;WITH RecursiveSplit AS (
    -- 递归锚点：取第一个分隔片段
    SELECT 
        1 AS ordinal,
        LEFT(@string, CHARINDEX('$', @string + '$') - 1) AS value,
        STUFF(@string, 1, CHARINDEX('$', @string + '$'), '') AS remaining_str
    UNION ALL
    -- 递归拆分剩余字符串
    SELECT 
        ordinal + 1,
        LEFT(remaining_str, CHARINDEX('$', remaining_str + '$') - 1),
        STUFF(remaining_str, 1, CHARINDEX('$', remaining_str + '$'), '')
    FROM RecursiveSplit
    WHERE remaining_str > ''
)
INSERT INTO @table (descr, meter)
SELECT 
    s1.value AS descr,
    CAST(s2.value AS INT) AS meter
FROM RecursiveSplit s1
INNER JOIN RecursiveSplit s2 
    ON s1.ordinal = s2.ordinal - 1
WHERE s1.ordinal % 2 = 1
-- 字符串长度超过100个分隔片段时需要加以下配置取消递归层数限制
OPTION (MAXRECURSION 0)

-- 测试查询
SELECT * FROM  @table

两种实现执行后的测试查询输出均符合要求：

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