DB2中基于不同起始位置提取子串 实现多专业字段与课程编号的关联映射

看起来你需要把MAJOR字段中用空格分隔的多个专业拆分成单独的行，然后和对应的COURSE_NUM做关联映射。针对这个需求，DB2有几种高效的实现方式，下面给你详细说明：

方法1：使用XMLTABLE（推荐） #

XMLTABLE是DB2中处理字符串拆分非常方便的工具，它可以把分隔符分隔的字符串转换成行集。这里我们先清理字段前后的空格，再用空格作为分隔符拆分：

WITH Req (COURSE_NUM, MAJOR) AS (
 VALUES
 ('A001', 'CS1 ')
 , ('A002', 'CS2 CS1 CS3 CS4')
 , ('A003', 'CS2 ')
 , ('B001', 'CS3 CS1 ')
 , ('B002', 'CS2 ')
)
SELECT 
    TRIM(m.major_item) AS MAJOR,
    r.COURSE_NUM
FROM Req r,
XMLTABLE(
    'tokenize(trim($MAJOR), '' '')'
    PASSING r.MAJOR AS "MAJOR"
    COLUMNS major_item VARCHAR(10) PATH '.'
) m
WHERE TRIM(m.major_item) <> ''; -- 过滤拆分后可能出现的空字符串

执行后会得到你期望的结果：

MAJOR COURSE_NUM
----- -----------
CS1   A001
CS2   A002
CS1   A002
CS3   A002
CS4   A002
CS2   A003
CS3   B001
CS1   B001
CS2   B002

代码说明： #

• tokenize(trim($MAJOR), ' ')：先清理MAJOR字段的首尾空格，再按空格拆分字符串。
• XMLTABLE将拆分后的每个子串转换成独立的行，major_item列存储单个专业名称。
• 最后的WHERE子句用于过滤因连续空格或首尾空格产生的空行。

方法2：递归CTE拆分字符串 #

如果你的DB2版本不支持XMLTABLE（比如较旧的版本），可以用递归CTE来实现拆分逻辑：

WITH Req (COURSE_NUM, MAJOR) AS (
 VALUES
 ('A001', 'CS1 ')
 , ('A002', 'CS2 CS1 CS3 CS4')
 , ('A003', 'CS2 ')
 , ('B001', 'CS3 CS1 ')
 , ('B002', 'CS2 ')
),
RecursiveSplit AS (
    -- 初始行：提取第一个专业，保留剩余未拆分的部分
    SELECT 
        COURSE_NUM,
        TRIM(SUBSTR(MAJOR, 1, LOCATE(' ', MAJOR || ' ') - 1)) AS MAJOR,
        TRIM(SUBSTR(MAJOR, LOCATE(' ', MAJOR || ' ') + 1)) AS REMAINING_MAJORS
    FROM Req
    WHERE TRIM(MAJOR) <> ''
    UNION ALL
    -- 递归步骤：持续拆分剩余的专业字符串
    SELECT 
        COURSE_NUM,
        TRIM(SUBSTR(REMAINING_MAJORS, 1, LOCATE(' ', REMAINING_MAJORS || ' ') - 1)) AS MAJOR,
        TRIM(SUBSTR(REMAINING_MAJORS, LOCATE(' ', REMAINING_MAJORS || ' ') + 1)) AS REMAINING_MAJORS
    FROM RecursiveSplit
    WHERE TRIM(REMAINING_MAJORS) <> ''
)
SELECT MAJOR, COURSE_NUM
FROM RecursiveSplit
WHERE MAJOR <> ''
ORDER BY MAJOR, COURSE_NUM;

这个递归CTE的逻辑是：

1. 初始查询提取每行的第一个专业，同时保留剩余未拆分的专业字符串。
2. 递归部分不断拆分剩余字符串，直到剩余部分为空。
3. 最后过滤空专业行并排序，得到目标结果。

关于你遇到的SQLCODE=-171错误的说明 #

你提到使用宿主变量时触发SQLCODE=-171，这个错误通常是因为LOCATE函数的参数不符合要求：

• 第一个参数（要查找的子串）的数据类型、长度或值无效，比如宿主变量定义为数值类型却传入字符串，或者宿主变量长度小于目标子串长度，甚至值为NULL（LOCATE的第一个参数不允许为NULL）。
• 也可能是宿主变量的长度设置不合理，比如要查找CS1但宿主变量长度仅为2，就会触发此错误。

不过上面的两种拆分方法已经能批量处理所有专业，不需要逐个用宿主变量处理，完全可以覆盖你的需求。

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