如何从多分隔符键值对字符串中提取HCMCostCenterMgr对应的邮箱？

现有字段DESCRIPTION的内容格式如下：

Entity=10||WorkdayReferenceID=9000100332||HCMCostCenterMgr=nicoleb@broadinstitute.org||FRP=||

需要提取其中HCMCostCenterMgr对应的邮箱地址，预期输出为：

nicoleb@broadinstitute.org

此前使用的SQL语句依赖固定位置的=号（第3个），新增FRP字段后，字段顺序或数量变化导致语句失效，需适配任意位置的HCMCostCenterMgr字段的提取方法。

通用字符串处理方案（适配多数数据库） #

核心是通过字段名精准定位，而非依赖固定顺序：

SELECT 
  TRIM(
    SUBSTR(
      TL.DESCRIPTION,
      -- 找到"HCMCostCenterMgr="的起始位置，加上字段名长度得到值的起点
      INSTR(TL.DESCRIPTION, 'HCMCostCenterMgr=') + LENGTH('HCMCostCenterMgr='),
      -- 计算从值起点到下一个"||"的长度
      INSTR(TL.DESCRIPTION, '||', INSTR(TL.DESCRIPTION, 'HCMCostCenterMgr=')) 
      - (INSTR(TL.DESCRIPTION, 'HCMCostCenterMgr=') + LENGTH('HCMCostCenterMgr='))
    )
  ) AS CCM
FROM YOUR_TABLE TL;

Oracle 专用正则方案 #

利用正则表达式直接提取目标内容，写法更简洁：

SELECT 
  REGEXP_SUBSTR(TL.DESCRIPTION, 'HCMCostCenterMgr=([^|]+)', 1, 1, NULL, 1) AS CCM
FROM YOUR_TABLE TL;

说明：正则HCMCostCenterMgr=([^|]+)匹配字段名后直到第一个|的所有字符，最后一个参数1指定提取括号内的分组内容。

MySQL/MariaDB 专用方案 #

借助SUBSTRING_INDEX多层截取：

SELECT 
  TRIM(
    SUBSTRING_INDEX(
      -- 截取"HCMCostCenterMgr="之后的所有内容
      SUBSTRING_INDEX(TL.DESCRIPTION, 'HCMCostCenterMgr=', -1),
      -- 截取到第一个"||"为止
      '||', 1
    )
  ) AS CCM
FROM YOUR_TABLE TL;

PostgreSQL 专用方案 #

方法一：分割函数组合

SELECT 
  SPLIT_PART(
    -- 先定位到目标字段所在的"||"分割块
    SPLIT_PART(TL.DESCRIPTION, '||', POSITION('HCMCostCenterMgr=' IN TL.DESCRIPTION) / LENGTH('HCMCostCenterMgr=||') + 1),
    -- 分割出字段值
    '=', 2
  ) AS CCM
FROM YOUR_TABLE TL;

方法二：正则提取

SELECT 
  (REGEXP_MATCH(TL.DESCRIPTION, 'HCMCostCenterMgr=([^|]+)'))[1] AS CCM
FROM YOUR_TABLE TL;

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