Posted by: Serge Rielau

Having demonstrated  how to aggregate strings using XML in an earlier post I have been asked about how to do the inverse.
Giving a a string and a defined separator, produce a table with one row per substring.
 
So here is what I came up with:

CREATE OR REPLACE FUNCTION PK_BASE.SPLIT(text VARCHAR(32000), split VARCHAR(10))
RETURNS TABLE(column_values 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;

Note that the order in which the strings are returned is not exactly defined.
Also note that th3 code supports empty strings. that is if to split-characters follow each other the matching string reported is empty (or NULL in VARCHAR2 mode)

SELECT * FROM TABLE(PK_BASE.SPLIT('123/45/6/789/abc/def/ghi', '/'));
 
COLUMN_VALUES
-------------------------
123
45
6
789
abc
def
ghi
 
 10 record(s) selected.

#Db2