DB2如何编写SELECT查询提取'-'分隔符前字符串并解决SUBSTRING参数报错

连续两次触发42815报错的核心是没有匹配DB2的函数内置约束和返回值特性：

• 最初尝试的写法存在两处适配偏差：CHARINDEX()是SQL Server专属函数，DB2无对应内置实现；同时DB2的字符串索引起始计数为1，SUBSTRING传起始位置0也会触发参数校验报错
• LOCATE()是DB2中替代CHARINDEX的位置查找函数，但其返回有明确规则：在目标字符串中未找到匹配的'-'时返回0；目标字符串本身为NULL时返回NULL
• SUBSTR()/SUBSTRING()函数的入参强制要求：起始位置、截取长度必须为非负有效数值，不接受负数、NULL作为入参

对应到两次错误写法的触发点：

1. 按LOCATE('-',department) - 1计算截取长度时，对无'-'的行，LOCATE返回0，计算结果为-1，直接触发参数非法报错
2. 追加department || '-'的写法没有处理Department字段本身为NULL的场景：NULL拼接任意字符串结果还是NULL，此时LOCATE对NULL入参返回NULL，计算得到的截取长度为NULL，依然不符合SUBSTR的入参要求

以下写法兼容所有场景：字段包含'-'、字段不含'-'、字段为NULL，不会触发参数报错：

SELECT
  SUBSTR(
    COALESCE(Department, ''),
    1,
    GREATEST(
      LOCATE('-', COALESCE(Department, '') || '-') - 1,
      0
    )
  ) AS DEPT
FROM College

逻辑说明 #

• 用COALESCE(Department, '')先把字段的NULL值统一替换为空字符串，避免后续函数收到NULL入参
• 给处理后的非空字符串拼接固定后缀'-'，保证LOCATE()必然能找到匹配的分隔符，不会返回0值
• 用GREATEST()对最终计算出的截取长度做兜底，确保传给SUBSTR的长度永远是大于等于0的非负数

场景匹配效果 #

• 对值为Commerce1-683877的行：返回分隔符前的内容Commerce1，符合预期需求
• 对值为Science2（无分隔符）的行：返回完整字段内容Science2
• 对值为NULL的行：返回空字符串，无报错

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