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.
- An expression that contains a list to be deconstructed. The value is cast to CLOB(2G).
- A character string expression that defines the separator between list elements. The value is cast to VARCHAR(32672).
- 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.
|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.|
NoteThis 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.
- 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
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;