02 - DB2 for i (EN)02a - SQL (EN)

SQL: Perfect cuts with SPLIT (EN)

Last Updated on 13 June 2021 by Roberto De Pedrini

Starting with IBM’s TR6 i 7.3, there is a new function that allows a string that contains values separated by a specific separator character to be subdivided into substrings. The SPLIT() function returns a table in which each row contains a single substring whose base string is composed.

The syntax is:

SPLIT ( expression , separator ) 

Where expression contains an alphanumeric value, and separator is the character that splits the portions of the string. Only a single separator character can be indicated. Technically, this is a User Defined Table Function (UDTF), which returns a table and therefore should be used with the specific syntax SELECT – FROM TABLE (… etc.))

Harder to explain than to understand… let’s immediately see a practical example, remembering that the SPLIT() function, unlike all other DB2 functions provided by IBM, is not in the QSYS2 library, but in the SYSTOOLS library.

This is our base string:

DATA_ORDINE=20190901|CLIENTE=ACBDE|ARTICOLO=4365

Substrings are separated by the pipe character ‘|’, here’s the statement that breaks down the string into its three components:

SELECT * FROM TABLE (systools.split('DATA_ORDINE=20190901|CLIENTE=ACBDE|ARTICOLO=4365', '|')) a

And here’s the result: two columns are returned, ORDINAL_POSITION (row number) and ELEMENT (substring value).

ORDINAL_POSITIONELEMENT
1DATA_ORDINE=20190901
2CLIENTE=ACBDE
3ARTICOLO=4365

Our base string actually also contains key-value pairs that are separated by the character ” = ” sequentially combining two SPLIT() we can get all the distinct values. Let’s see how to do this, taking as a base a sample string that contains a list of parameters according to the style of a URL (parameters separated by “&”, keys and values separated by “=”)

-- Split parameters
WITH a AS ( SELECT ordinal_position num1, CHAR(element, 50) el1 
FROM TABLE (systools.split('DATA=20190901&CLIENTE=015731&ARTICOLO=43705', '&')) s1 )
-- Separates key from value
, b AS ( SELECT num1, ordinal_position num2, CHAR(element, 50) el2 
FROM a JOIN TABLE (systools.split(a.el1, '=')) s2 ON 1=1 )
-- Build key and value on the same row
SELECT b.num1 riga, b.el2 chiave, COALESCE(b2.el2, '*ERR') valore 
FROM b LEFT JOIN b b2 ON b.num1=b2.num1 AND b2.num2=2 WHERE b.num2=1

Here’s the final result:

rigachiavevalore
1DATA20190901
2CLIENTE015731
3ARTICOLO43705

With the first SPLIT you “cut” the string at the “&”, with the second you separate the values on the equal sign, with the last SELECT you recompose the whole into well-ordered rows. In case of missing separators, the constant “*ERR” will appear to highlight the situation.

At this link you can find the IBM documentation of the function.

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

Senior IBM i Analyst/Developer and technical writer. Former collaborator of the Italian edition of "System i News" magazine and author of several publications about tools and development practices for the IBM i platform.

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *