Comma separated search and search with checkboxes in Oracle APEX

When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.

Here’s an example:

Your SQL statement probably looks like this:

select CUSTOMER_ID,
       CUST_FIRST_NAME,
       CUST_LAST_NAME,
       CUST_STREET_ADDRESS1,
       CUST_CITY,
       CUST_STATE,
       CUST_POSTAL_CODE,
       CUST_EMAIL,
       CREDIT_LIMIT
  from DEMO_CUSTOMERS
 where CUSTOMER_ID = :P4_SEARCH

When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.

There’re a couple of options you have, I’ll list three below:

1. INSTR

   where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0
   

2. REGEXP_LIKE

   where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')
   

3. REGEXP_SUBSTR

   where customer_id in to_number((
     select regexp_substr(:P4_SEARCH,'[^,]+', 1, level) 
       from dual
    connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null 
   ))
   

4. APEX_STRING

    where customer_id in (select * from table(apex_string.split(:P4_SEARCH,',')))
   

Which one to choose? It depends what you need… if you need readability, maybe you find APEX_STRING or INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).

The Explain Plan for the first SQL looks like this:

The Explain Plan for the last SQL looks like this:

The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:

The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:

where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0

Happy searching your Classic Report :)

Update 23-FEB after feedback from Martin and FaTechs.:
There are many other alternatives, but I do want to highlight one more if you’re using APEX 5.1 there’s a nice package called apex_string which can help too.

where customer_id in (select * from table(apex_string.split(:P4_SEARCH,',')))

You can see more examples of apex_string in the documentation or on Carsten’s blog.