SQL多值模式匹配替换 字符串列同类异名值统一实现方案

错误写法原因说明 #

你之前的两种写法存在明显缺陷：

• REPLACE函数单次只能替换固定字符串，要覆盖大小写、单复数的所有变体需要嵌套十几层写法，冗余且难以维护
• CASE WHEN逻辑只要命中任意条件就会将整行内容直接替换为单个水果名，会直接丢失同字段内的其他水果内容，完全不符合多值字段的部分替换需求

方案1：正则替换链式调用（适合分类较少的场景） #

用支持正则、全局匹配、忽略大小写的REGEXP_REPLACE函数嵌套调用即可实现需求，不同SQL引擎的写法略有差异：

PostgreSQL 示例 #

SELECT 
  REGEXP_REPLACE(
    REGEXP_REPLACE(
      REGEXP_REPLACE(fruit, '\m(apple|apples)\M', 'Apple', 'gi'),
      '\m(banana|bananas)\M', 'Banana', 'gi'
    ),
    '\m(strawberry|strawberries)\M', 'Strawberry', 'gi'
  ) AS fruit
FROM your_table;

参数说明：

• \m/\M是单词边界标识符，避免匹配到包含目标词的其他无关词汇
• g表示全局替换，匹配到所有符合规则的内容都替换
• i表示忽略大小写，自动匹配大小写不同的变体

MySQL 8.0+ 示例 #

SELECT 
  REGEXP_REPLACE(
    REGEXP_REPLACE(
      REGEXP_REPLACE(fruit, '[[:<:]](apple|apples)[[:>:]]', 'Apple', 1, 0, 'i'),
      '[[:<:]](banana|bananas)[[:>:]]', 'Banana', 1, 0, 'i'
    ),
    '[[:<:]](strawberry|strawberries)[[:>:]]', 'Strawberry', 1, 0, 'i'
  ) AS fruit
FROM your_table;

方案2：拆分-匹配-聚合（适合分类多、需后续扩展的场景） #

如果后续会新增更多水果的匹配规则，更推荐用拆分字段逐个匹配再聚合的方案，维护成本更低：

1. 将每行的多值字段按逗号拆分为单个水果元素
2. 把每个元素和预设的标准映射规则匹配，替换为标准名，未匹配到的内容原样保留
3. 把处理后的元素重新拼接为多值字符串

PostgreSQL 示例 #

-- 定义映射规则，新增分类只需在这里加一行即可
WITH fruit_mapping AS (
  SELECT 'Apple' AS standard, ARRAY['apple', 'apples'] AS variants
  UNION ALL
  SELECT 'Banana' AS standard, ARRAY['banana', 'bananas'] AS variants
  UNION ALL
  SELECT 'Strawberry' AS standard, ARRAY['strawberry', 'strawberries'] AS variants
)
SELECT 
  STRING_AGG(COALESCE(m.standard, TRIM(f.item)), ', ') AS fruit
FROM your_table t,
     -- 拆分多值字段为单行元素
     UNNEST(STRING_TO_ARRAY(t.fruit, ',')) AS f(item)
-- 关联映射表匹配标准名
LEFT JOIN fruit_mapping m 
  ON LOWER(TRIM(f.item)) = ANY(m.variants)
-- 按原表主键分组，保证行级对应关系
GROUP BY t.主键字段;

该方案完全不需要修改核心逻辑，新增分类仅需在映射规则里添加对应配置，适配性更强。

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