PostgreSQL查询：判断[]内字符串是否为数据库独立条目

假设你的表名为icd10_synonyms，存储同义词的列名为synonym（请根据实际情况替换），以下是实现需求的SQL查询及详细解释：

核心查询：标记括号内文本是否存在独立条目 #

SELECT
  s.synonym AS 原始条目,
  regexp_replace(s.synonym, '.*\[([^\]]+)\].*', '\1') AS 括号内文本,
  CASE WHEN EXISTS (
    SELECT 1
    FROM icd10_synonyms s2
    WHERE s2.synonym = regexp_replace(s.synonym, '.*\[([^\]]+)\].*', '\1')
  ) THEN '存在独立条目' ELSE '不存在' END AS 独立条目状态
FROM icd10_synonyms s
WHERE s.synonym ~ '\[([^\]]+)\]';

关键部分解释 #

1. 筛选带括号的条目
   WHERE s.synonym ~ '\[([^\]]+)\]'：用正则匹配所有包含[xxx]格式的记录，其中：

   • \[：转义左括号（正则中[是特殊字符，必须转义才能匹配实际的左括号）
   • ([^\]]+)：匹配并捕获括号内的所有内容（[^\]]表示非右括号的任意字符，+表示至少一个字符）

2. 提取括号内文本
   regexp_replace(s.synonym, '.*\[([^\]]+)\].*', '\1')：通过正则替换提取括号内的文本：

   • .*\[：匹配左括号之前的所有字符
   • ([^\]]+)：捕获括号内的目标文本（第一个捕获组）
   • \].*：匹配右括号之后的所有字符
   • '\1'：将整个字符串替换为第一个捕获组的内容，即括号内的文本

3. 检查独立条目是否存在
   EXISTS子查询会在表中查找是否存在完全匹配括号内文本的独立记录，返回布尔值后通过CASE语句转换成易读的状态描述。

扩展查询：找出缺失独立条目的记录 #

如果需要快速定位哪些括号内文本没有对应的独立条目，可以使用以下查询：

SELECT
  s.synonym AS 带括号条目,
  regexp_replace(s.synonym, '.*\[([^\]]+)\].*', '\1') AS 缺失的独立条目
FROM icd10_synonyms s
WHERE s.synonym ~ '\[([^\]]+)\]'
AND NOT EXISTS (
  SELECT 1
  FROM icd10_synonyms s2
  WHERE s2.synonym = regexp_replace(s.synonym, '.*\[([^\]]+)\].*', '\1')
);

对之前ChatGPT方案的说明 #

• 第一个方案使用的REGEXP_SUBSTR是Oracle SQL的函数，PostgreSQL不支持，因此无效。
• 第二个方案仅筛选出带括号的条目，未关联表进行独立条目的存在性检查，因此无法满足你的需求。

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