SQL: Perfect cuts with SPLIT (EN)

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).

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:

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.