How to check if an array contains a particular string?

oracle plsql contains user-defined-types
user1394523 picture user1394523 · Jan 29, 2013 · Viewed 33.3k times · Source

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?

Answer

Egor Skriptunoff picture Egor Skriptunoff · Jan 29, 2013

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.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More