我们想对一个列的内容根据分隔符，进行拆分

创建一个function来拆分字符串
DROP FUNCTION SPLIT;
CREATE FUNCTION SPLIT (
    TEXT VARCHAR(32000)
    ,split VARCHAR(10)
    )
RETURNS TABLE (element VARCHAR(60))
RETURN
WITH rec(rn, column_value, pos) AS (
        VALUES (
            1
            ,VARCHAR(SUBSTR(TEXT, 1, DECODE(INSTR(TEXT, split, 1), 0, LENGTH(TEXT), INSTR(TEXT, split, 1) - 1)), 255)
            ,INSTR(TEXT, split, 1) + LENGTH(split)
            )
        
        UNION ALL
        
        SELECT rn + 1
            ,VARCHAR(SUBSTR(TEXT, pos, DECODE(INSTR(TEXT, split, pos), 0, LENGTH(TEXT) - pos + 1, INSTR(TEXT, split, pos) - pos)), 255)
            ,INSTR(TEXT, split, pos) + LENGTH(split)
        FROM rec
        WHERE rn < 30000
            AND pos > LENGTH(split)
        )

SELECT column_value
FROM rec


SELECT phone
FROM emp
JOIN TABLE(split('John,Jack,Jill', ','))
ON emp.name = split.element;

 PHONE
 ----------
 9055551234
 4165554321
       NULL


另外一个创建函数的用法，可以认为是返回了一个字符串数组
CREATE TYPE VARCHARS AS VARCHAR(6) ARRAY[]@
CREATE FUNCTION SPLIT(text VARCHAR(32000), split VARCHAR(10))
RETURNS VARCHARS
BEGIN
DECLARE i INTEGER DEFAULT 1;
DECLARE pos INTEGER DEFAULT 1;
DECLARE len INTEGER;
DECLARE res VARCHARS;
WHILE pos < LENGTH(text) DO
SET len = DECODE(INSTR(text, split, pos),
0,
LENGTH(text) - pos + 1,
INSTR(text, split, pos) - pos);
SET res[i] = SUBSTR(text, pos, len);
SET i = i + 1;
SET pos = pos + len + 1;
END WHILE;
RETURN res;
END@

DB2通过UNNEST或者TABLE来把字符串数组转化成一个单个列的表
db2 => select * from UNNEST(split('John,Jack,Jill', ',')) AS split(element)

ELEMENT
-------
John   
Jack   
Jill   

  3 record(s) selected.

db2 => select * from TABLE(split('John,Jack,Jill', ',')) AS split(element)

ELEMENT
-------
John   
Jack   
Jill   

  3 record(s) selected.

db2 =>

DB2 9.7
SELECT phone
FROM emp
JOIN UNNEST(split('John,Jack,Jill', ',')) AS split(element)
ON emp.name = split.element
@

DB2 9.7.5
SELECT phone
FROM emp
JOIN TABLE(split('John,Jack,Jill', ',')) AS split(element)
ON emp.name = split.element
@