Oracle含斜杠分隔符列数据拆分查询实现方案咨询

假设你的表名为your_table_name，存储目标数据的列名为col，可以通过以下SQL语句实现需求：

基础版本（仅处理含斜杠的行） #

WITH parsed_data AS (
    SELECT
        col,
        -- 提取固定前缀：第一个斜杠前的内容去掉最后一个字符
        SUBSTR(col, 1, INSTR(col, '/') - 2) AS prefix,
        -- 提取所有需要拼接的字母：第一个斜杠前的最后一个字母 + 所有斜杠后的字母
        SUBSTR(col, INSTR(col, '/') - 1, 1) || REGEXP_REPLACE(SUBSTR(col, INSTR(col, '/')), '/', '') AS letters
    FROM your_table_name
    -- 仅处理包含斜杠的行
    WHERE INSTR(col, '/') > 0
)
SELECT
    prefix || SUBSTR(letters, LEVEL, 1) AS result
FROM parsed_data
CONNECT BY
    LEVEL <= LENGTH(letters)
    AND PRIOR col = col
    AND PRIOR SYS_GUID() IS NOT NULL;

增强版本（兼容无斜杠的行） #

如果存在不需要拆分的行（无斜杠），可以用下面的语句直接输出原数据：

WITH parsed_data AS (
    SELECT
        col,
        CASE WHEN INSTR(col, '/') > 0 THEN
            SUBSTR(col, 1, INSTR(col, '/') - 2)
        ELSE
            col
        END AS prefix,
        CASE WHEN INSTR(col, '/') > 0 THEN
            SUBSTR(col, INSTR(col, '/') - 1, 1) || REGEXP_REPLACE(SUBSTR(col, INSTR(col, '/')), '/', '')
        ELSE
            ''
        END AS letters
    FROM your_table_name
)
SELECT
    CASE WHEN letters = '' THEN prefix ELSE prefix || SUBSTR(letters, LEVEL, 1) END AS result
FROM parsed_data
CONNECT BY
    (LEVEL <= LENGTH(letters) OR letters = '')
    AND PRIOR col = col
    AND PRIOR SYS_GUID() IS NOT NULL
AND (letters = '' AND LEVEL = 1 OR letters != '');

逻辑说明 #

1. CTE parsed_data：
   • 提取固定前缀：从行首到第一个斜杠前的内容，去掉最后一个字符（比如100-G-100A/B的前缀是100-G-100）。
   • 提取字母串：把第一个斜杠前的最后一个字母，和所有斜杠后的字母拼接成无分隔符的字符串（比如100-G-100A/B的字母串是AB）。
2. 主查询：
   • 用CONNECT BY生成与字母串长度匹配的行数，每行取字母串中的一个字符，和前缀拼接得到最终拆分结果。
   • PRIOR SYS_GUID() IS NOT NULL用于避免不同行的拆分结果交叉，保证每个原始行的拆分独立。

内容的提问来源于stack exchange，提问作者김용현