如何高效将值解析为多列？现有SQL解析脚本优化咨询

我需要将一个值解析为最多三个子值，示例如下：

我已经用以下SQL实现了需求，但想知道有没有更高效、代码更简洁的写法：

cast([UnitNum] as char(5)) as MasterUnit,
left(cast([UnitNum] as char(5)), 3) as Unit1,
case
when (left(left(cast([UnitNum] as char(5)), 2) + right(cast([UnitNum] as char(5)), 2),3)) = left(cast([UnitNum] as char(5)), 2)
then NULL
else (left(left(cast([UnitNum] as char(5)), 2) + right(cast([UnitNum] as char(5)), 2),3))
end as Unit2,
case
when (left(cast([UnitNum] as char(5)), 2)) + (right(cast([UnitNum] as char(5)), 1)) = left([UnitNum],2)
then NULL
else (left(cast([UnitNum] as char(5)), 2) + right(cast([UnitNum] as char(5)), 1))
end as Unit3

现有代码存在大量重复的CAST([UnitNum] AS CHAR(5))和嵌套字符串操作，既影响性能也不易维护。可以通过提前复用转换结果、简化判断逻辑来优化：

方法1：使用CTE复用转换结果 #

WITH UnitCTE AS (
    SELECT CAST([UnitNum] AS CHAR(5)) AS MasterUnit
    FROM YourTable
)
SELECT 
    MasterUnit,
    LEFT(MasterUnit, 3) AS Unit1,
    -- 仅当第4位非空格时生成Unit2
    CASE WHEN SUBSTRING(MasterUnit, 4, 1) <> ' ' THEN LEFT(MasterUnit, 2) + SUBSTRING(MasterUnit, 4, 1) END AS Unit2,
    -- 仅当第5位非空格时生成Unit3
    CASE WHEN SUBSTRING(MasterUnit, 5, 1) <> ' ' THEN LEFT(MasterUnit, 2) + SUBSTRING(MasterUnit, 5, 1) END AS Unit3
FROM UnitCTE;

方法2：使用CROSS APPLY定义变量 #

如果不想用CTE，也可以用CROSS APPLY一次性转换并复用结果：

SELECT 
    u.MasterUnit,
    LEFT(u.MasterUnit, 3) AS Unit1,
    CASE WHEN SUBSTRING(u.MasterUnit, 4, 1) <> ' ' THEN LEFT(u.MasterUnit, 2) + SUBSTRING(u.MasterUnit, 4, 1) END AS Unit2,
    CASE WHEN SUBSTRING(u.MasterUnit, 5, 1) <> ' ' THEN LEFT(u.MasterUnit, 2) + SUBSTRING(u.MasterUnit, 5, 1) END AS Unit3
FROM YourTable
CROSS APPLY (SELECT CAST([UnitNum] AS CHAR(5)) AS MasterUnit) u;

1. 减少重复计算：只执行一次CAST([UnitNum] AS CHAR(5))，后续所有字段都基于这个结果计算，降低性能开销
2. 简化逻辑：直接通过判断CHAR(5)补全的空格来确定是否存在对应子值，替代原代码复杂的嵌套字符串比较
3. 可读性提升：代码结构更清晰，每一步操作的意图明确，便于维护

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