如何在Informatica中将列数据按ID转换为多行

针对你需要将NCLOB类型的分号分隔角色值按ID拆分成多行的需求，以下是两种可行的Oracle SQL解决方案：

方法一：使用CONNECT BY + 正则函数 #

适用于所有支持正则函数的Oracle版本（11g及以上），通过递归生成行来拆分字符串：

SELECT 
    id_col,
    TRIM(REGEXP_SUBSTR(TO_CLOB(roles_nclob), '[^;]+', 1, LEVEL)) AS role
FROM your_table
CONNECT BY 
    LEVEL <= REGEXP_COUNT(TO_CLOB(roles_nclob), '[^;]+')
    AND PRIOR id_col = id_col
    AND PRIOR SYS_GUID() IS NOT NULL
-- 过滤拆分后可能出现的空字符串
WHERE TRIM(REGEXP_SUBSTR(TO_CLOB(roles_nclob), '[^;]+', 1, LEVEL)) IS NOT NULL;

关键说明： #

• TO_CLOB(roles_nclob)：将NCLOB转换为CLOB，确保正则函数能正常处理大字段
• REGEXP_SUBSTR(..., '[^;]+', 1, LEVEL)：按分号拆分字符串，LEVEL表示当前拆分的是第N个元素
• CONNECT BY LEVEL <= REGEXP_COUNT(...)：控制生成的行数等于拆分后的元素总数
• PRIOR SYS_GUID() IS NOT NULL：避免同一ID下出现循环递归问题

方法二：使用XMLTABLE #

适用于Oracle 11gR2及以上版本，利用XML的字符串拆分功能，代码更简洁：

SELECT 
    t.id_col,
    TRIM(x.role) AS role
FROM your_table t,
     XMLTABLE(
         'tokenize(.,'';'')'
         PASSING TO_CLOB(t.roles_nclob)
         COLUMNS role VARCHAR2(200) PATH '.'
     ) x
WHERE TRIM(x.role) IS NOT NULL;

关键说明： #

• XMLTABLE通过tokenize函数直接按分号拆分传入的CLOB内容
• COLUMNS role VARCHAR2(200) PATH '.'：定义拆分后生成的列名和类型，可根据实际角色长度调整为CLOB类型
• 同样通过TO_CLOB转换NCLOB，确保兼容性

测试示例 #

假设你的表your_table有如下数据：

执行上述任意SQL后，将得到预期结果：

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