New table function to break apart values in columns and fields

A couple of years, and a few Technical Refreshes ago, the LISTAGG SQL built in function was added to Db2 for i. Until the latest TR there was not some easy way to do the opposite, take a string from one column and break it into separate results. I am not saying it was not possible to do this before, it has just got a whole lot easier with the introduction of the SPLIT table function in the lastest IBM i 7.3 TR6.

The syntax of this new table function is as follows:

SPLIT(input list or column,separator character)

SPLIT returns two columns in its results:

1. ELEMENT:  the values extracted from the "input list". This is a CLOB variable that is 2 gigabytes in size.
2. ORDINAL_POSITION:  Not ORDINAL as is given in the IBM documentation for SPLIT. This is the relative position of the value returned in ELEMENT from the original "input list".

Here is an example using an "input list", or a string to you and I, of names.

01 SELECT VARCHAR(TRIM(ELEMENT),20) AS "Name", 02 ORDINAL_POSITION AS "Pos" 03 FROM TABLE(SYSTOOLS.SPLIT ('SIMON WILLIAM ALFRED JENIFER JOESPH THOMAS',' ')) 04 ORDER BY ELEMENT

Line 1: I do not want ELEMENT to be 2 gigabytes, therefore, I am using the VARCHAR function to convert the CLOB to a variable length character column of a maximum of 20 positions. And I renaming this column "Name".

Line 2: I am renaming the ORDINAL_POSITION column to "Pos".

Line 3: This is a standard table function statement. SPLIT is in the library SYSTOOLS. I give my string of values as SPLIT's first parameter and space as the separator character.

Line 4: I want my results to be sorted in name order.

My results look like:

Name Pos ------- --- ALFRED 3 JENIFER 4 JOESPH 5 SIMON 1 THOMAS 6 WILLIAM 2

Let me now make this a bit more real world using a field/column from a file/table rather than a string. I going to start with the same table I used in my example of LISTAGG. My first table, TABLE1 contains a list of things that belong to a type.

SELECT * FROM MYLIB.TABLE1 TYPE THING ------- ----------- ANIMAL ELK ANIMAL HAWK ANIMAL MEERKAT ANIMAL ORCA ANIMAL SEA LION FRUIT APPLE, FUJI FRUIT BLACKBERRY FRUIT GRAPEFRUIT FRUIT GUAVA FRUIT RASPBERRY

I am going to take this data, use LISTAGG to combine the things into two rows, and insert the results into another second table, TABLE2.

01 INSERT INTO MYLIB.TABLE2 02 SELECT TYPE,LISTAGG(RTRIM(THING),':') 03 WITHIN GROUP(ORDER BY THING) 04 FROM MYLIB.TABLE1 05 GROUP BY TYPE

Line 2: I don't like using spaces or commas ( , ) as separators as I have found they are often used within strings. Therefore, I am using the colon ( : ) as my separator.

TABLE2 now has a column I can use SPLIT upon.

SELECT * FROM MYLIB.TABLE2 TYPE LIST_OF_THINGS ------ ------------------------------------------------- ANIMAL ELK:HAWK:MEERKAT:ORCA:SEA LION FRUIT APPLE, FUJI:BLACKBERRY:GRAPEFRUIT:GUAVA:RASPBERRY

Here is my SQL statement to break LIST_OF_THINGS into its individual elements.

01 SELECT TYPE, 02 VARCHAR(TRIM(ELEMENT),20) AS "New thing", 03 ORDINAL_POSITION AS "Pos" 04 FROM MYLIB.TABLE2, 05 TABLE(SYSTOOLS.SPLIT(LIST_OF_THINGS,':'))

The above statement is pretty similar to the one I started this post with, but here I have a table and a table function.

Line 1: I want the TYPE column from TABLE2.

Line 2: As I did before I am converting the ELEMENT column in the results to be a varchar type column.

Line 3: I want the ordinal position too.

Line 4: Data is to come from TABLE2.

Line 5: Notice that there is no join need for the SPLIT table function, just a comma separates it from the previous table. I just pass SPLIT the column I want split and the separator character.

My results are just what I wanted, just like the contents of TABLE1.

TYPE New thing Pos ------- ----------- --- ANIMAL ELK 1 ANIMAL HAWK 2 ANIMAL MEERKAT 3 ANIMAL ORCA 4 ANIMAL SEA LION 5 FRUIT APPLE, FUJI 1 FRUIT BLACKBERRY 2 FRUIT GRAPEFRUIT 3 FRUIT GUAVA 4 FRUIT RASPBERRY 5

 

I can certainly see me using SPLIT to break out repeating data in long strings of information. It is so easy to use I could, in a RPG program, place the results from the SPLIT into a data structure array and process each element individually.

 

You can learn more about the SPLIT table function from the IBM website here.

 

This article was written for IBM i 7.4 and 7.3 TR6.