Comparing Strings

I just received an interesting question regarding report filtering. The problem was the following:

1. There was a checkbox with multiple choices in the form.
2. The data would be saved as a concatenated string of values
(String1:String2:String3).
3. The problem was in filtering that column since the filter would also be the same checkbox containing multiple values.


The solution for that is quite simple. All you need to do is to create a function which will compare the two strings and return something if it finds a match.

CREATE OR REPLACE FUNCTION compare_checkbox_strings (
   p_checkbox   IN   VARCHAR2,
   p_column     IN   VARCHAR2
)
   RETURN NUMBER
IS
   l_vc_arr2   apex_application_global.vc_arr2;
   v_count     NUMBER;
BEGIN
   l_vc_arr2 := apex_util.string_to_table (p_checkbox);

   FOR i IN 1 .. l_vc_arr2.COUNT
   LOOP
      EXIT WHEN v_count > 0;
      v_count := INSTR (':' || p_column || ':', ':' || l_vc_arr2 (i) || ':');
   END LOOP;

   IF v_count > 0
   THEN
      RETURN 1;
   ELSE
      RETURN 0;
   END IF;
END compare_checkbox_strings;

Now, you can use it in your SQL Query like this:

SELECT *
  FROM your_table
 WHERE compare_checkbox_strings (:p1_your_checkbox, your_column) = 1;

You can find a working example here:
http://apex.oracle.com/pls/otn/f?p=31517:275