PostgreSQL多条件提取子串：拆分分号值匹配起止行

原表数据 #

col1                                start    end
p.[A138T;S160G;D221_E222delinsGK]   138      138
p.[A138T;S160G;D221_E222delinsGK]   160      160
p.[A138T;S160G;D221_E222delinsGK]   221      222

需求 #

将col1中用分号分隔的各值拆分到独立行，使每个值对应原表中该行的start和end，预期输出：

col1                                start    end
p.A138T                             138      138
p.S160G                             160      160
p.D221_E222delinsGK                 221      222

问题代码 #

你尝试的查询语句对第三行无效，原代码：

select
       case 
            when start = "end" and col1 like 'p.[%' then 'p.'||(regexp_match(col1, '([A-Z]'||start||'[A-Z])'))[1] 
            when start != "end" and col1 like 'p.[%' then 'p.'||(regexp_match(col1, '[A-Z\d+_delins]+'||start||'[A-Z\d+_delins]+'))[1] 
            else col1,
                   
            start,
            end
from table

解决方案 #

你的正则匹配逻辑依赖start值去原字符串中查找，容易出现匹配不准确（比如第三行的221-222在字符串中是连续数字段，正则写法无法正确捕获）。更可靠的方式是先拆分col1的分号分隔项，再按顺序和原表行关联：

with numbered_rows as (
    -- 给原表每行标记序号，对应col1中分隔项的顺序
    select 
        col1, 
        start, 
        "end", 
        row_number() over () as rn
    from your_table_name
),
split_variants as (
    -- 提取col1括号内的内容，拆分成带序号的项
    select 
        'p.' || val as col1,
        rn
    from numbered_rows,
         unnest(string_to_array(substring(col1 from 'p\.\[(.*)\]'), ';')) 
             with ordinality as split_val(val, rn)
)
-- 关联序号，得到最终结果
select 
    sv.col1,
    nr.start,
    nr."end"
from numbered_rows nr
join split_variants sv on nr.rn = sv.rn;

代码说明 #

1. numbered_rows：给原表每行添加序号，因为原表的三行正好对应col1中三个分号分隔项的顺序。
2. split_variants：
   • 用substring(col1 from 'p\.\[(.*)\]')提取p.[...]中括号内的内容；
   • 用string_to_array将提取的内容按分号拆分成数组；
   • 用unnest ... with ordinality将数组展开为多行，并保留每个项的序号；
   • 给每个项拼接p.前缀，得到目标格式的col1值。
3. 最后通过序号关联两个CTE，得到每个拆分项对应的start和end值。

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