SPLIT table function

Edit online

The SPLIT table function returns a result table that contains one row for each substring of input-list that is separated by delimiter.

Authorization: None required.

input-list: An expression that contains a list to be deconstructed. The value is cast to CLOB(2G).
delimiter: A character string expression that defines the separator between list elements. The value is cast to VARCHAR(32672).
escape: A character string expression with a length of 1 that defines a character that is used to escape a delimiter sequence. If this parameter is provided, any delimiter sequence of characters immediately proceeded by this character will not be interpreted as a delimiter. The escape character will be removed from the returned element value.

The table function returns one row for each substring of input-list containing the characters between delimiter strings. If input-list contains two adjacent delimiter strings, an empty string is returned to represent an element with no content. If a delimiter string starts at position 1 of input-list, a row containing a zero length string is returned as the first element. If a delimiter string ends at the last position of input-list, a row containing a zero length string is returned as the last element. Substrings of input-list that match delimiter are not included in the result.

If input-list is null, the result contains no rows. If delimiter is null, the empty string, or a string that is not found, input-list is returned.

The result of the function is a table containing a row for each substring of input-list. The columns of the result table are described in the following table. The result columns are nullable.

Table 1. SPLIT table function
Column Name	Data Type	Description
ORDINAL_POSITION	INTEGER	The relative position of this element in the input string. The first row has a value of 1.
ELEMENT	CLOB(2G)	The value of the element.

Note

This function is provided in the SYSTOOLS schema as an example of how to break a string apart at a delimiting character by using an SQL table function. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.

Example

Return a list of all authorities collected for all users for objects in the APP1 schema. First, a common table expression breaks the detailed authority lists into separate rows for each authority using the SPLIT table function. Then, all the authorities for the object are recombined into a single list for each user, removing duplicate authorities and listing them alphabetically, using the LISTAGG aggregate function.

WITH EXPANDED_AUTHS AS (
SELECT AUTHORIZATION_NAME, OBJECT_NAME, VARCHAR(TRIM(ELEMENT), 10) AS AUTH
  FROM QSYS2.AUTHORITY_COLLECTION, 
       TABLE(SYSTOOLS.SPLIT(DETAILED_REQUIRED_AUTHORITY, ' ')) 
  WHERE OBJECT_SCHEMA = 'APP1' 
        AND CHECK_ANY_AUTHORITY = 0)
SELECT AUTHORIZATION_NAME, OBJECT_NAME, 
  LISTAGG(DISTINCT AUTH, ' ') WITHIN GROUP(ORDER BY AUTH) 
  FROM EXPANDED_AUTHS 
  GROUP BY AUTHORIZATION_NAME, OBJECT_NAME 
  ORDER BY AUTHORIZATION_NAME, OBJECT_NAME;