如何在SQL中拆分单元格内的层级数据并规整为列

需求：将单元格内的层级结构文本（如Sector 1 > Building 123 > Wing B > Ground Floor或Sector 2 > Building 123 > wing A）拆分为Sector、Building、wing、floor四列，层级缺失时对应列填充null。

不同数据库的实现代码 #

MySQL 版本 #

利用SUBSTRING_INDEX按分隔符>拆分字符串，再提取每个片段的有效内容：

SELECT
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(original_str, '>', 1), ' ', -1)) AS Sector,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(original_str, '>', 2), ' ', -1)) AS Building,
    UPPER(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(original_str, '>', 3), ' ', -1))) AS wing,
    CASE 
        WHEN LENGTH(original_str) - LENGTH(REPLACE(original_str, '>', '')) >= 3
        THEN TRIM(SUBSTRING_INDEX(original_str, '>', -1))
        ELSE NULL
    END AS floor
FROM your_table_name;

注意：替换your_table_name为实际表名，original_str为存储层级文本的列名。

PostgreSQL 版本 #

通过STRING_TO_ARRAY将文本转为数组，再按索引提取对应层级内容：

WITH split_data AS (
    SELECT
        original_str,
        STRING_TO_ARRAY(TRIM(BOTH ' ' FROM REPLACE(original_str, ' > ', '>')), '>') AS level_array
    FROM your_table_name
)
SELECT
    SPLIT_PART(level_array[1], 'Sector ', 2) AS Sector,
    SPLIT_PART(level_array[2], 'Building ', 2) AS Building,
    UPPER(SPLIT_PART(level_array[3], 'Wing ', 2)) AS wing,
    CASE WHEN array_length(level_array, 1) >= 4 THEN level_array[4] ELSE NULL END AS floor
FROM split_data;

注意：先统一分隔符格式（去掉>前后的空格），避免拆分后出现多余空格。

SQL Server 版本 #

用CHARINDEX定位分隔符位置，逐步截取每个层级的内容：

SELECT
    -- 提取Sector编号
    TRIM(RIGHT(SUBSTRING(original_str, 1, CHARINDEX('>', original_str) - 1), 
               LEN(SUBSTRING(original_str, 1, CHARINDEX('>', original_str) - 1)) - CHARINDEX(' ', SUBSTRING(original_str, 1, CHARINDEX('>', original_str) - 1)))) AS Sector,
    -- 提取Building编号
    TRIM(RIGHT(SUBSTRING(original_str, CHARINDEX('>', original_str) + 1, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - CHARINDEX('>', original_str) - 1), 
               LEN(SUBSTRING(original_str, CHARINDEX('>', original_str) + 1, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - CHARINDEX('>', original_str) - 1)) - CHARINDEX(' ', SUBSTRING(original_str, CHARINDEX('>', original_str) + 1, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - CHARINDEX('>', original_str) - 1)))) AS Building,
    -- 提取wing标识并转大写
    UPPER(TRIM(RIGHT(SUBSTRING(original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) + 1, 
                               ISNULL(NULLIF(CHARINDEX('>', original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) + 1), 0) - CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - 1, 
                                      LEN(original_str) - CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - 1)), 
                     LEN(SUBSTRING(original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) + 1, 
                                   ISNULL(NULLIF(CHARINDEX('>', original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) + 1), 0) - CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - 1, 
                                          LEN(original_str) - CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - 1)) - CHARINDEX(' ', SUBSTRING(original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) + 1, 
                                                                                                                                                   ISNULL(NULLIF(CHARINDEX('>', original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) + 1), 0) - CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - 1, 
                                                                                                                                                          LEN(original_str) - CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) - 1)))))) AS wing,
    -- 提取floor内容，缺失则为null
    CASE 
        WHEN LEN(original_str) - LEN(REPLACE(original_str, '>', '')) >= 3
        THEN TRIM(SUBSTRING(original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str, CHARINDEX('>', original_str) + 1) + 1) + 1, LEN(original_str)))
        ELSE NULL
    END AS floor
FROM your_table_name;

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