Add a builtin table function that splits a character string

Db2 12 adds the useful LISTAGG function that aggregates a set of string values into a single string.
It would be good to have an inverse: a table function SPLIT that takes a string and a separator, and returns a table of string values.
The signature would be similar to
CREATE FUNCTION SPLIT(str VARCHAR(30000), delim VARCHAR(100))
RETURNS TABLE (
SEQ INTEGER, VALUE VARCHAR(30000)
)

Actually, this function is pretty easy to write as an SQL table function. It would be nicer to have it in the engine, though -- to be symmetrical with LISTAGG.

Post comment

Guest

Reply
| Feb 26, 2019
Not only is that function "nice to have" for symmetricity with LISTAGG. Much more importantly, it serves a very concrete purpose: It helps with IN lists of unknown length.

Suppose we have a search criteria that checks whether a column's value is one of a list of given values, where the length of that list is not known at development time. The statement would look like
```
SELECT something FROM table WHERE colval IN (?, ?, ?)
```
Since we do not know the number of values, we have to construct the IN list dynamically, trashing the dynamic statement cache since each list length leads to a "new" statement that is not found in the DSC.

With the SPLIT function, one could write the query as
```
SELECT something FROM table WHERE colval IN (SELECT VALUE FROM FUNCTION(SPLIT(?)))
```
Where the list of values would be fed into the parameter marker as a single, comma-separated string of values. Alas, only one single statement in the DSC!

An alternative solution could use temporary tables, but this technique is much easier to implement.
0 reply Hide replies

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

Add a builtin table function that splits a character string