Postgres 13.7：从处方列提取指定药品子串的查询方案

针对处方表中某列提取指定药品子串的需求，推荐使用正则匹配+聚合拼接的方案，扩展性强，新增药品只需修改正则规则即可。

核心查询语句 #

假设表名为prescriptions，目标列为column_a，指定药品为Medicine A、Medicine B、Medicine C，查询语句如下：

SELECT 
  string_agg(match, ' ') AS column_a
FROM (
  SELECT 
    column_a,
    unnest(regexp_matches(column_a, 'Medicine (A|B|C)', 'g')) AS match
  FROM prescriptions
) AS matched_rows
GROUP BY column_a
HAVING string_agg(match, ' ') <> ''
ORDER BY column_a;

语句说明 #

• regexp_matches(column_a, 'Medicine (A|B|C)', 'g')：全局扫描目标列，匹配所有符合Medicine A/B/C格式的子串，返回匹配结果数组
• unnest：将匹配结果数组拆分为多行记录
• string_agg(match, ' ')：将同一原始行的所有匹配药品用空格拼接成字符串
• HAVING子句：过滤未匹配到任何指定药品的行，对应示例中无目标药品的空输出

扩展性优化 #

若后续新增指定药品（如Medicine D），仅需修改正则表达式中的药品列表，将'Medicine (A|B|C)'改为'Medicine (A|B|C|D)'即可，无需调整查询整体结构。

输入输出验证 #

对示例输入执行该查询后，将得到与期望完全一致的输出：

• 输入Medicine B and Medicine D → 输出Medicine B
• 输入2 x day Medicine A and 1 shot of Medicine C → 输出Medicine A Medicine C
• 输入Medicine E and Medicine D → 无输出（被HAVING过滤）

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