SQL CASE语句多匹配分组异常，如何按首次出现单词匹配？

原CASE语句的问题在于它是按条件顺序匹配，只要Subject包含'animal'就会优先返回'Animal'，完全忽略'car'是否出现得更早。要实现按首次出现的目标单词返回结果，核心是比较目标单词在字符串中的首次出现位置，选择位置最靠前的那个对应的类别。

通用思路 #

通过字符串位置函数获取每个目标单词在Subject中的首次出现位置，比较这些位置，返回位置最小的单词对应的类别；如果某个单词不存在，位置函数会返回0，需要特殊处理。

MySQL 实现 #

使用LOCATE()函数获取子串首次出现的位置（不存在则返回0）：

SELECT
  Subject,
  CASE
    -- 两个单词都不存在时返回NULL
    WHEN LOCATE('animal', Subject) = 0 AND LOCATE('car', Subject) = 0 THEN NULL
    -- animal出现更早，或car不存在时返回Animal
    WHEN LOCATE('animal', Subject) < LOCATE('car', Subject) OR LOCATE('car', Subject) = 0 THEN 'Animal'
    -- 否则返回Car（car出现更早，或animal不存在）
    ELSE 'Car'
  END AS Category
FROM your_table;

PostgreSQL 实现 #

使用POSITION()函数，逻辑和MySQL一致：

SELECT
  Subject,
  CASE
    WHEN POSITION('animal' IN Subject) = 0 AND POSITION('car' IN Subject) = 0 THEN NULL
    WHEN POSITION('animal' IN Subject) < POSITION('car' IN Subject) OR POSITION('car' IN Subject) = 0 THEN 'Animal'
    ELSE 'Car'
  END AS Category
FROM your_table;

SQL Server 实现 #

使用CHARINDEX()函数：

SELECT
  Subject,
  CASE
    WHEN CHARINDEX('animal', Subject) = 0 AND CHARINDEX('car', Subject) = 0 THEN NULL
    WHEN CHARINDEX('animal', Subject) < CHARINDEX('car', Subject) OR CHARINDEX('car', Subject) = 0 THEN 'Animal'
    ELSE 'Car'
  END AS Category
FROM your_table;

可选：精确匹配完整单词 #

如果需要避免匹配包含目标单词的更长词汇（比如避免把'animals'识别为'animal'），可以结合正则表达式的单词边界匹配：

MySQL 精确匹配版本 #

SELECT
  Subject,
  CASE
    WHEN REGEXP_INSTR(Subject, '[[:<:]]animal[[:>:]]') = 0 AND REGEXP_INSTR(Subject, '[[:<:]]car[[:>:]]') = 0 THEN NULL
    WHEN REGEXP_INSTR(Subject, '[[:<:]]animal[[:>:]]') < REGEXP_INSTR(Subject, '[[:<:]]car[[:>:]]') OR REGEXP_INSTR(Subject, '[[:<:]]car[[:>:]]') = 0 THEN 'Animal'
    ELSE 'Car'
  END AS Category
FROM your_table;

PostgreSQL 精确匹配版本 #

SELECT
  Subject,
  CASE
    WHEN STRPOS(Subject, '\manimal\m') = 0 AND STRPOS(Subject, '\mcar\m') = 0 THEN NULL
    WHEN STRPOS(Subject, '\manimal\m') < STRPOS(Subject, '\mcar\m') OR STRPOS(Subject, '\mcar\m') = 0 THEN 'Animal'
    ELSE 'Car'
  END AS Category
FROM your_table;

SQL Server 精确匹配版本 #

SELECT
  Subject,
  CASE
    WHEN PATINDEX('%[[:<:]]animal[[:>:]]%', Subject) = 0 AND PATINDEX('%[[:<:]]car[[:>:]]%', Subject) = 0 THEN NULL
    WHEN PATINDEX('%[[:<:]]animal[[:>:]]%', Subject) < PATINDEX('%[[:<:]]car[[:>:]]%', Subject) OR PATINDEX('%[[:<:]]car[[:>:]]%', Subject) = 0 THEN 'Animal'
    ELSE 'Car'
  END AS Category
FROM your_table;

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