Using APEX_STRING.SPLIT and APEX_STRING.JOIN for Cleaner Multi Value Handling

The Multi Value Problem

Shuttle items, checkbox groups, and multi select lists in APEX return their values as colon delimited strings like 10:20:30. Working with these strings in PL/SQL has traditionally been awkward, requiring custom tokenizer functions or convoluted INSTR/SUBSTR logic. The APEX_STRING package provides clean, efficient utilities for splitting and joining these values.

Splitting Strings Into Collections

APEX_STRING.SPLIT converts a delimited string into a table that you can use directly in SQL:

-- Delete employees selected in a shuttle item
DELETE FROM employees
 WHERE department_id IN (
    SELECT TO_NUMBER(column_value)
      FROM TABLE(APEX_STRING.SPLIT(:P10_DEPARTMENTS, ':'))
 );

-- Use in a WHERE clause for filtering
SELECT employee_id, first_name, last_name, department_id
  FROM employees
 WHERE department_id IN (
    SELECT TO_NUMBER(column_value)
      FROM TABLE(APEX_STRING.SPLIT(:P10_DEPARTMENTS, ':'))
 );

The default delimiter is colon, which matches APEX’s internal format, but you can specify any delimiter character as the second argument.

Joining Collections Into Strings

The reverse operation, APEX_STRING.JOIN, concatenates a collection back into a delimited string:

DECLARE
    l_values APEX_T_VARCHAR2;
BEGIN
    SELECT department_id
      BULK COLLECT INTO l_values
      FROM departments
     WHERE location_id = :P10_LOCATION_ID;

    :P10_DEPARTMENTS := APEX_STRING.JOIN(l_values, ':');
END;
/

Other Useful APEX_STRING Functions

The package includes several other gems. APEX_STRING.FORMAT works like C’s sprintf for building messages with substitution placeholders. APEX_STRING.PUSH appends a value to an existing APEX_T_VARCHAR2 collection. APEX_STRING.SHUFFLE randomizes a collection, which is useful for randomizing quiz questions or display order.

In APEX 23.1 and later, the APEX_STRING.SPLIT function also supports splitting CLOBs, which is useful for parsing large imported text files or log data. Prior to 23.1, you would need to use DBMS_LOB functions to break up large CLOBs before splitting them.

Performance Note

For small lists (under 100 values), APEX_STRING.SPLIT is fast and convenient. For very large delimited strings with thousands of values, consider whether a different data model might be more appropriate. If you find yourself routinely splitting strings with hundreds of values, the data probably belongs in a proper relational table rather than a colon delimited session item.