4.5.3.3.2 Working with Delimited String Values

To work delimited values in SQL or PL/SQL, use the apex_string.split() function.

Consider a Select Many page item named P5_SELECTED_SIZES that uses the vertical bar (|) as delimiter. If you configured its list of values using a query with SIZE_DESCRIPTION as the display value and SIZE_CODE as the return value, then after the user selects multiple sizes, the value of P5_SELECTED_SIZES might look like the string "SM|MD|XXL".

An initial thought might use the bind variable as follows:

select id, sku, price
  from items
 where item_size in (:P5_SELECTED_SIZES) /* Tempting, but problematic */

However, attempting this when there are multiple delimited values in the page item returns no rows because this syntax only matches an item row with an ITEM_SIZE value of the literal value "SM|MD|XXL".

The solution involves splitting the string into its separate values using the SPLIT() function in the APEX_STRING package. Then you can select the COLUMN_VALUE from its single-column table result. Notice the example below passes the vertical bar ('|') as the second argument to indicate the value delimiter in use:

select id, sku, price
  from items
 where item_size in (select column_value
                       from apex_string.split(:P5_SELECTED_SIZES,'|'))

To work with the multiple values in PL/SQL, you can define a variable of type apex_t_varchar2. This is APEX's built-in list of strings type. Then you can call the same split() function:

declare
   l_selected_sizes apex_t_varchar2;
begin
   l_selected_sizes := apex_string.split(:P5_SELECTED_SIZES,'|');
   for j in 1..l_selected_sizes.count loop
      -- Use the j-th size code here
      -- l_selected_sizes(j)
   end loop;
end;