Wednesday, June 19, 2019

New table function to break apart values in columns and fields

suing string able function to break long strings into smaller parts

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.

6 comments:

  1. Quite handy

    ReplyDelete
  2. SPLIT and LISTAGG look great. What I can't figure out is how to use SPLIT when there is more than one space in between the values. Using your example above, if the list looks like this: ('SIMON WILLIAM ALFRED JENIFER JOESPH THOMAS',' '), I get multiple blank rows between SIMON and WILLIAM. Must I add something in the WHERE to exclude these rows?

    ReplyDelete
    Replies
    1. I would make sure that there is only one space between the names.

      Delete
  3. Great!. Due %split PTF is not installed, I have tried embed to an SQLRPGLE source, the Split SQL function to split a string and assign it to a dimensioned data structure by adding INTO to the first example in this way "Exec SQL
    SELECT
    VARCHAR(TRIM(ELEMENT), 20) AS "Name",
    ORDINAL_POSITION AS "Pos"
    Into :MyDsSplit
    FROM TABLE(SYSTOOLS.SPLIT
    ('SIMON WILLIAM ALFRED JENIFER JOESPH THOMAS',' ')); ".

    However, the SQL0312 Reason: The host structure is a dimensioned array error has occurred. Is possible to solve this scenario?. Thanks and regards.

    ReplyDelete
    Replies
    1. It is not possible to output from a SPLIT into an array.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.