SPLIT table function

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	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. Creating customized versions of this table function to better suit a specific need is encouraged. Use the Insert Generated SQL feature in IBM i Access Client Solutions (ACS) to extract the source for this function. Then modify it and create a new procedure in 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;