动态字符串层级拆分问题：短串最后项无法映射至level_Attribute

问题分析 #

当前代码通过固定索引$[9]获取Level_Attribute，但动态长度的字符串拆分后数组长度不固定，短字符串的最后一项索引远小于9，导致无法赋值到目标字段。需要一种能自动定位最后一项的方法，无论字符串拆分后有多少个元素。

解决方案（SQL Server） #

方法1：使用STRING_SPLIT（SQL Server 2022+） #

利用STRING_SPLIT的WITH ORDINALITY参数保留元素顺序，再通过总元素数定位最后一项：

-- 处理单个字符串示例
DECLARE @str VARCHAR(100)='A.B.C:D';
SET @str = REPLACE(@str, ':', '.'); -- 统一分隔符

WITH SplitCTE AS (
    SELECT 
        value,
        ordinal
    FROM STRING_SPLIT(@str, '.', 1) -- 1启用ORDINALITY，保留元素顺序
),
TotalCTE AS (
    SELECT COUNT(*) AS TotalCount FROM SplitCTE
)
SELECT 
    @str AS TheOriginal,
    MAX(CASE WHEN ordinal = 1 THEN value END) AS Level_0,
    MAX(CASE WHEN ordinal = 2 THEN value END) AS Level_1,
    MAX(CASE WHEN ordinal = 3 THEN value END) AS Level_2,
    MAX(CASE WHEN ordinal = 4 THEN value END) AS Level_3,
    -- 按需添加更多Level_N字段
    MAX(CASE WHEN ordinal = (SELECT TotalCount FROM TotalCTE) THEN value END) AS Level_Attribute
FROM SplitCTE;

方法2：兼容SQL Server 2016-2019版本（无ORDINALITY支持） #

通过XML拆分结合ROW_NUMBER()生成元素顺序：

-- 处理表中所有字符串示例
DECLARE @YourTable TABLE (StringColumn VARCHAR(100));
INSERT INTO @YourTable VALUES 
('A.B.C:D.E.F:G.H.I:J'),
('A.B.C:D');

WITH SplitCTE AS (
    SELECT 
        t.StringColumn,
        LTRIM(RTRIM(m.n.value('.[1]','varchar(100)'))) AS value,
        ROW_NUMBER() OVER (PARTITION BY t.StringColumn ORDER BY (SELECT NULL)) AS ordinal
    FROM (
        SELECT 
            StringColumn,
            CAST('<XMLRoot><RowData>' + REPLACE(REPLACE(StringColumn, ':', '.'), '.', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM @YourTable t
    ) t
    CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
),
TotalCTE AS (
    SELECT 
        StringColumn,
        COUNT(*) AS TotalCount
    FROM SplitCTE
    GROUP BY StringColumn
)
SELECT 
    sc.StringColumn AS TheOriginal,
    MAX(CASE WHEN sc.ordinal = 1 THEN sc.value END) AS Level_0,
    MAX(CASE WHEN sc.ordinal = 2 THEN sc.value END) AS Level_1,
    MAX(CASE WHEN sc.ordinal = 3 THEN sc.value END) AS Level_2,
    MAX(CASE WHEN sc.ordinal = 4 THEN sc.value END) AS Level_3,
    MAX(CASE WHEN sc.ordinal = 5 THEN sc.value END) AS Level_4,
    -- 按需扩展更多Level_N字段
    MAX(CASE WHEN sc.ordinal = tc.TotalCount THEN sc.value END) AS Level_Attribute
FROM SplitCTE sc
JOIN TotalCTE tc ON sc.StringColumn = tc.StringColumn
GROUP BY sc.StringColumn;

核心逻辑说明 #

1. 统一分隔符：先把所有:替换为.，确保拆分规则一致。
2. 拆分并保序：拆分字符串为行，同时记录每个元素的顺序序号。
3. 定位最后一项：通过统计每个字符串的总元素数，将序号等于总元素数的元素赋值给Level_Attribute。
4. 动态映射层级：用CASE语句按序号映射到对应的Level_0、Level_1...字段，没有对应元素的字段会返回NULL（可通过ISNULL(..., '')替换为空字符串）。

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