Performing Set Operations on Strings

Question

Consider these two examples in PL/SQL:

Rule: Only valid choices are from menu.

These values are variables and not rows in a table and the desired output gets assigned to a variable. What tools can I use to return menu items that are not chosen?

--Case #1

onmenu := '06,02,00,03';
choice := '06,01,00';

-- Desired Output: Because Choices 06 and 00 are not in Error. 
-- Leaving 02,03 as remaining options.
'02,03'


--Case #2

onmenu := '06,02,00';
choice := '00,01,06';

-- Desired Output: Because only choice 01 is in error
-- respond with unchosen option.

'02'

Answer 1

Split the comma separated string into rows, put it in a collection, use multiset operator to substract choice from onmenu. Here is one option:

DECLARE
  TYPE t_vc_array IS TABLE OF VARCHAR2(100);  
  l_onmenu VARCHAR2(100);
  l_choice VARCHAR2(100);
  l_onmenu_a  t_vc_array; 
  l_choice_a  t_vc_array; 
  l_result_a  t_vc_array;  

  FUNCTION split_to_array (l_string VARCHAR2) RETURN t_vc_array
  IS
    l_returnval t_vc_array;
  BEGIN
    l_returnval := t_vc_array();
      WITH rws as (
        SELECT l_string str FROM dual
      )
        SELECT regexp_substr (
                 str,
                 '[^,]+',
                 1,
                 LEVEL
               ) value
        BULK COLLECT INTO l_returnval       
        FROM   rws
        CONNECT BY LEVEL <= 
          LENGTH ( str ) - LENGTH ( REPLACE ( str, ',' ) ) + 1;    
    RETURN l_returnval;
  END;
BEGIN
  l_onmenu := '06,02,00,03';
  l_choice := '06,01,00';
   
  l_onmenu_a := split_to_array(l_onmenu);
  l_choice_a := split_to_array(l_choice);
  l_result_a  := l_onmenu_a MULTISET EXCEPT l_choice_a;
  FOR i IN 1 .. l_result_a.COUNT LOOP
    dbms_output.put_line(l_result_a(i));
  END LOOP;
END;
/

02
03


PL/SQL procedure successfully completed.