PostgreSQL 11中基于特定字符与字符串匹配拆分列值至多行

针对你的需求，这里提供一个更可靠的查询方案，能精准处理包含placebo且带-的col3字段拆分，同时保留其他行的原有格式：

SELECT 
    t.col1,
    t.col2,
    TRIM(split_part) AS col3
FROM 
    your_table t
CROSS JOIN LATERAL (
    SELECT 
        unnest(
            CASE 
                WHEN t.col3 ILIKE '%placebo%' AND t.col3 ~* '-' 
                THEN regexp_split_to_array(t.col3, '\s*-\s*')
                ELSE ARRAY[t.col3]
            END
        ) AS split_part
) s
ORDER BY 
    t.col1;

为什么你的原查询会有问题？ #

你的初始尝试把unnest放在CASE表达式中作为SELECT列表项，这种写法存在两个主要问题：

• 空格处理不彻底：直接用string_to_array(col3, '-')拆分时，会保留-前后的空格（比如拆分Placebo - IR Prednisone会得到Placebo 和 IR Prednisone，带有多余空格）
• 逻辑灵活性不足：当不需要拆分时，CASE返回的是单个字符串，而unnest需要数组类型，虽然PostgreSQL会隐式转换，但这种写法在复杂场景下容易出错。

方案详解 #

• CROSS JOIN LATERAL：这是PostgreSQL中处理行级动态集合的关键，它允许我们针对每一行数据动态生成拆分后的结果行
• regexp_split_to_array(t.col3, '\s*-\s*')：用正则表达式匹配-前后的任意空格，拆分时自动忽略这些空格，确保拆分后的字符串干净
• CASE条件判断：仅当col3包含placebo（不区分大小写）且存在-时才拆分，否则将原col3包装成单元素数组，保证非拆分场景下只返回一行
• TRIM函数：最后再修剪拆分后的字符串，彻底消除可能残留的首尾空格

执行这个查询后，你的示例数据会完美输出你期望的结果：

col1 col2 col3
NCT00065442 APC-Placebo apc
NCT00065442 APC-Placebo placebo
NCT00135226 Placebo placebo
NCT00146640 MR Prednisone mr prednisone
NCT00146640 Placebo - IR Prednisone placebo
NCT00146640 Placebo - IR Prednisone ir prednisone

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