Oracle Apex 5 - Checkbox list in where clause not working, why?

Question

I have a quirky issue. I have a checkbox, a reports table and i'd like to click a checkbox or multi check boxes to return a result. In the Database I have a saved list of checkbox results seperated by a colon :

It all works except when I check box 'A' and 'C'. which is the first and last result in the database. I can click A and B or just A or just C or C and B but when i click A and C it doesn't work.

any clue?

select ...

from ...

where 

AND (CASE WHEN :P1_BENEFITS_TAG IS NOT NULL 
     THEN instr( ':'|| UPPER(b.BENEFITS_TAGS)||':',
                 ':'|| UPPER(:P1_BENEFITS_TAG) ||':' )
     ELSE 1 
     END) > 0

Answer 1

It looks like your database row contains the string 'A:B:C:'.

When :P1_BENEFITS_TAG is any of the values 'A:B', 'B:C', 'A', 'B' or 'C' then the database row "contains" that value and your INSTR expression will return a number > 0.

However, 'A:B:C' does not contain the string 'A:C', so the INSTR will return 0 for that.