Oracle SQL：基于非结构化评论创建STATUS列的匹配问题求助

针对非结构化评论列ANES.ANESTH_COMMENTS创建STATUS列的需求，核心要解决大小写敏感、单词部分匹配、标点干扰这几个问题，以下是不同数据库环境下的可行SQL实现：

通用思路 #

1. 启用不区分大小写的匹配，覆盖所有大小写变体（比如Approved/approved/APPROVED）
2. 匹配独立单词，避免误匹配包含目标词的更长词汇（比如不要把yesman识别为yes）
3. 自动忽略目标词前后的标点、空格等干扰字符

具体实现 #

Oracle/PostgreSQL #

使用REGEXP_LIKE结合单词边界\m/\M和不区分大小写参数'i'：

SELECT
  ANESTH_COMMENTS,
  CASE
    -- 匹配Approved/approve/yes/either/verified的任意独立单词变体
    WHEN REGEXP_LIKE(ANESTH_COMMENTS, '\m(approved|approve|yes|either|verified)\M', 'i') THEN 'Approved'
    -- 匹配Denied/Not approved/no/declined的任意独立单词变体
    WHEN REGEXP_LIKE(ANESTH_COMMENTS, '\m(denied|not approved|no|declined)\M', 'i') THEN 'Denied'
    ELSE 'Not specified'
  END AS STATUS
FROM ANES;

MySQL #

MySQL 5.x使用REGEXP加单词边界[[:<:]]/[[:>:]]，MySQL 8.0+可直接用REGEXP_LIKE加'i'参数：

-- MySQL 5.x版本
SELECT
  ANESTH_COMMENTS,
  CASE
    WHEN ANESTH_COMMENTS REGEXP '[[:<:]](approved|approve|yes|either|verified)[[:>:]]' THEN 'Approved'
    WHEN ANESTH_COMMENTS REGEXP '[[:<:]](denied|not approved|no|declined)[[:>:]]' THEN 'Denied'
    ELSE 'Not specified'
  END AS STATUS
FROM ANES;

-- MySQL 8.0+版本（更清晰）
SELECT
  ANESTH_COMMENTS,
  CASE
    WHEN REGEXP_LIKE(ANESTH_COMMENTS, '[[:<:]](approved|approve|yes|either|verified)[[:>:]]', 'i') THEN 'Approved'
    WHEN REGEXP_LIKE(ANESTH_COMMENTS, '[[:<:]](denied|not approved|no|declined)[[:>:]]', 'i') THEN 'Denied'
    ELSE 'Not specified'
  END AS STATUS
FROM ANES;

SQL Server #

SQL Server 2016+支持REGEXP_LIKE，低版本用PATINDEX结合大小写不敏感排序规则：

-- SQL Server 2016+版本
SELECT
  ANESTH_COMMENTS,
  CASE
    WHEN REGEXP_LIKE(ANESTH_COMMENTS, '\b(approved|approve|yes|either|verified)\b', 'i') THEN 'Approved'
    WHEN REGEXP_LIKE(ANESTH_COMMENTS, '\b(denied|not approved|no|declined)\b', 'i') THEN 'Denied'
    ELSE 'Not specified'
  END AS STATUS
FROM ANES;

-- SQL Server 2016之前版本
SELECT
  ANESTH_COMMENTS,
  CASE
    -- 处理目标词在中间、开头、结尾三种情况，覆盖独立单词匹配
    WHEN PATINDEX('%[^a-zA-Z](approved|approve|yes|either|verified)[^a-zA-Z]%', ANESTH_COMMENTS COLLATE SQL_Latin1_General_CP1_CI_AS) > 0
         OR PATINDEX('^(approved|approve|yes|either|verified)[^a-zA-Z]%', ANESTH_COMMENTS COLLATE SQL_Latin1_General_CP1_CI_AS) > 0
         OR PATINDEX('%[^a-zA-Z](approved|approve|yes|either|verified)$', ANESTH_COMMENTS COLLATE SQL_Latin1_General_CP1_CI_AS) > 0 THEN 'Approved'
    WHEN PATINDEX('%[^a-zA-Z](denied|not approved|no|declined)[^a-zA-Z]%', ANESTH_COMMENTS COLLATE SQL_Latin1_General_CP1_CI_AS) > 0
         OR PATINDEX('^(denied|not approved|no|declined)[^a-zA-Z]%', ANESTH_COMMENTS COLLATE SQL_Latin1_General_CP1_CI_AS) > 0
         OR PATINDEX('%[^a-zA-Z](denied|not approved|no|declined)$', ANESTH_COMMENTS COLLATE SQL_Latin1_General_CP1_CI_AS) > 0 THEN 'Denied'
    ELSE 'Not specified'
  END AS STATUS
FROM ANES;

关键说明 #

• 大小写处理：通过正则的'i'参数或COLLATE指定不区分大小写的排序规则，确保所有大小写变体都能匹配
• 单词边界：使用\m/\M、[[:<:]]/[[:>:]]或\b限定匹配独立单词，避免误匹配包含目标词的长词
• 短语匹配：正则分组|支持直接匹配Not approved这类多词短语，无需拆分处理
• 长文本兼容：正则匹配对超过200字符的评论无性能问题，能适配任意格式的非结构化内容

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