How to check if an array contains a particular string?

Question

I have an array of strings. I want to check if a particular string is present in the array.

DECLARE
  TYPE v_array IS TABLE OF VARCHAR2(200);
  ais_array v_array;
BEGIN
  ais_array := ('Lb1','Lb2','Lb3','Lb613');
  IF 'Lb1' IN ais_array THEN
     dbms_output.put_line('found');
  END IF;
END;

The IN operator is not working. I tried doing a select * on the type and then using IN but that didn't work either.

Any suggestions?

Quell

As Oracle has a built-in collection type called VARRAY, v_array is a bad name for a nested table type. It could confuse a stupid person. – Warthman

Answer 1

Try member of condition:

IF 'Lb1' member of ais_array THEN
  dbms_output.put_line('found');
END IF;

Oracle introduced several set operators for working with collections in 10g. Please read the documentation to learn more.

Schmooze

Great!! This is what i was looking for. simple n clean. Thanks a lot :-) – Quell

This doesn't work actually. MEMBER OF works only for nested tables. – Fermat

@SaisumanthGopisetty - The question was about nested tables. Not about VARRAY. – Schmooze

Answer 2

MEMBER OF can only be used with nested tables. You're trying to use it on an associative array. You are going to get an error "wrong number or types of arguments in call to 'MEMBER OF' ".

This is how to use it:

    DECLARE  
   TYPE clientele IS TABLE OF VARCHAR2 (64);  

   client_list_12    clientele := clientele ('Customer 1', 'Customer 2');  
   client_list_13    clientele := clientele ('Customer 1', 'Customer 3');  

   client_list_133   clientele  
                   := clientele ('Customer 1', 'Customer 3', 'Customer 3');  

   client_list_empty clientele := clientele ();                          
BEGIN  
   IF 'Customer 1' MEMBER OF client_list_12  
   THEN  
      DBMS_OUTPUT.put_line ('Customer 1 is in the 12 list');  
   END IF;  

   IF 'Customer 2' NOT MEMBER OF client_list_13  
   THEN  
      DBMS_OUTPUT.put_line ('Customer 2 is not in the 13 list');  
   END IF;  

   DBMS_OUTPUT.put_line ('List 133 contains ' || CARDINALITY (client_list_133) || ' items');  

   IF client_list_empty IS EMPTY  
   THEN  
      DBMS_OUTPUT.put_line ('Client list is empty');  
   END IF;  

   IF client_list_133 IS NOT EMPTY  
   THEN  
      DBMS_OUTPUT.put_line ('Client list 133 is not empty');  
   END IF;  

END; 

Portfolio