SQL字符串拆分多列优化咨询：如何改进重复SUBSTRING写法

需要将格式为[类型/层级] - 编码的字符串拆分为vTipo、vNivel、vCodigo三列，测试数据如下：

DECLARE @TABLE TABLE ([vDescricao] VARCHAR(100))
    
INSERT INTO @TABLE
SELECT 
    [descricao]
FROM 
(
    VALUES
    ('[A/3] - 7'),
    ('[B/3] - 50'),
    ('[C/2] - 1.3.1'),
    ('[D/1] - 1.4'),
    ('[E/3] - 33'),
    ('[F/4f] - 1.2.1.2.1'),
    ('[GG/4] -  1.2.1.4.2')
)D([descricao]);

已实现的拆分代码可正常运行，但存在重复调用CHARINDEX的冗余逻辑：

SELECT 
    [vDescricao]

    ,[vTipo] = SUBSTRING(
        vDescricao,
        CHARINDEX('[', vDescricao)+1,
        CHARINDEX('/', vDescricao) - CHARINDEX('[', vDescricao)-1
        )

    ,[vNivel] = SUBSTRING(
        vDescricao,
        CHARINDEX('/', vDescricao)+1,
        CHARINDEX(']', vDescricao) - CHARINDEX('/', vDescricao)-1
        )

    ,[vCodigo] = SUBSTRING(
        vDescricao,
        CHARINDEX(' - ', vDescricao)+3,
        LEN(vDescricao) - CHARINDEX(' - ', vDescricao)
        )

FROM @TABLE 

以下是几种更优的解决方案：

方案1：用CROSS APPLY存储中间索引（消除重复逻辑） #

通过CROSS APPLY一次性计算所有需要的索引位置，后续直接引用，既提升可读性也减少重复计算：

SELECT 
    t.vDescricao,
    [vTipo] = SUBSTRING(t.vDescricao, idx.OpenBracket + 1, idx.Slash - idx.OpenBracket - 1),
    [vNivel] = SUBSTRING(t.vDescricao, idx.Slash + 1, idx.CloseBracket - idx.Slash - 1),
    [vCodigo] = LTRIM(SUBSTRING(t.vDescricao, idx.Dash + 3, LEN(t.vDescricao) - idx.Dash - 2))
FROM @TABLE t
CROSS APPLY (
    SELECT 
        CHARINDEX('[', t.vDescricao) AS OpenBracket,
        CHARINDEX('/', t.vDescricao) AS Slash,
        CHARINDEX(']', t.vDescricao) AS CloseBracket,
        CHARINDEX(' - ', t.vDescricao) AS Dash
) idx;

注：LTRIM用于处理vCodigo列前的空格（如最后一条数据的 1.2.1.4.2），不需要可直接删除。

方案2：创建自定义函数（复用拆分逻辑） #

如果需要在多个查询中复用该拆分逻辑，可创建表值函数封装逻辑：

CREATE FUNCTION dbo.ParseDescricao(@vDescricao VARCHAR(100))
RETURNS @Result TABLE (
    vTipo VARCHAR(50),
    vNivel VARCHAR(50),
    vCodigo VARCHAR(50)
)
AS
BEGIN
    DECLARE 
        @OpenBracket INT = CHARINDEX('[', @vDescricao),
        @Slash INT = CHARINDEX('/', @vDescricao),
        @CloseBracket INT = CHARINDEX(']', @vDescricao),
        @Dash INT = CHARINDEX(' - ', @vDescricao);

    INSERT INTO @Result
    SELECT
        SUBSTRING(@vDescricao, @OpenBracket + 1, @Slash - @OpenBracket - 1),
        SUBSTRING(@vDescricao, @Slash + 1, @CloseBracket - @Slash - 1),
        LTRIM(SUBSTRING(@vDescricao, @Dash + 3, LEN(@vDescricao) - @Dash - 2));

    RETURN;
END;

使用方式：

SELECT 
    t.vDescricao,
    f.vTipo,
    f.vNivel,
    f.vCodigo
FROM @TABLE t
CROSS APPLY dbo.ParseDescricao(t.vDescricao) f;

方案3：利用JSON转换（简洁写法） #

将原字符串转换为JSON格式，通过JSON_VALUE提取字段，适合SQL Server 2016及以上版本：

SELECT 
    t.vDescricao,
    [vTipo] = JSON_VALUE(jsonStr, '$.tipo'),
    [vNivel] = JSON_VALUE(jsonStr, '$.nivel'),
    [vCodigo] = LTRIM(JSON_VALUE(jsonStr, '$.codigo'))
FROM @TABLE t
CROSS APPLY (
    SELECT 
        REPLACE(
            REPLACE(
                REPLACE(t.vDescricao, '[', '{"tipo":"'),
                '/', '","nivel":"'),
            '] - ', '","codigo":"') + '"})' AS jsonStr
) j;

注：该方式依赖字符串格式完全一致，若原字符串包含双引号等特殊字符需额外处理。

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