MySQL如何裁剪字符串可变字符 实现字段格式统一匹配

核心规律梳理 #

对照原始数据与目标结果，可提炼统一处理规则：

• 目标值固定以4位年份开头，整体由4段内容通过3个下划线拼接
• 原始值存在两类差异：部分行开头带UPL_冗余前缀，部分行直接以年份开头；所有行末尾带文件扩展名（.csv/.xlsx），部分csv文件在目标段后还带有_COST/_DRG冗余后缀

通用实现SQL（支持MySQL 8.0/5.7） #

该写法无需提前枚举前缀内容，自动定位年份起始位置，适配后续可能出现的其他前缀场景：

SELECT
  SUBSTRING_INDEX(
    -- 定位4位年份+下划线的起始位置，裁掉前面所有冗余前缀
    SUBSTRING(col, REGEXP_INSTR(col, '[0-9]{4}_')),
    '_',
    -- 按下划线分割后取前4段，刚好得到含3个下划线的目标串，自动裁掉后续冗余后缀和扩展名
    4
  ) AS col1
FROM your_table_name;

低版本兼容SQL（支持MySQL 5.6及更早版本） #

针对当前给出的固定前缀样本，无正则函数也可实现：

SELECT
  SUBSTRING_INDEX(
    -- 识别UPL_前缀做固定偏移裁剪
    IF(col LIKE 'UPL_%', SUBSTRING(col, 5), col),
    '_',
    4
  ) AS col1
FROM your_table_name;

执行结果验证 #

对提供的原始样本执行上述SQL，返回结果完全匹配预期：

函数逻辑说明 #

• REGEXP_INSTR(col, '[0-9]{4}_')：返回连续4位数字+下划线的首次出现位置，即年份段的起始下标
• SUBSTRING(字符串, 起始位置)：从指定下标开始截取字符串到末尾，实现前缀裁剪
• SUBSTRING_INDEX(字符串, 分隔符, 计数)：按指定分隔符拆分字符串，返回前N个分段拼接的结果，计数传4时刚好得到4段内容，包含3个下划线
• 低版本写法中IF(col LIKE 'UPL_%', SUBSTRING(col,5), col)：针对已知固定前缀做偏移裁剪，适合前缀无变化的存量数据场景

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