How to test if a string is inside a list of predefined strings?

I defined a list of strings, which contains different country codes (like USA,CHINA,HK,JPN, etc.).

How can I check, if an input variable is the country code in the list?

I use the following code to test, but it fails.

declare
 country_list  CONSTANT VARCHAR2(200) := USA,CHINA,HK,JPN;
 input VARCHAR2(200);
begin
 input  := 'JPN';
 IF input   IN  (country_list)
         开发者_开发知识库DBMS_OUTPUT.PUT_LINE('It is Inside');
    else       
         DBMS_OUTPUT.PUT_LINE('It is not  Inside');
 END IF;
end;

---

If you can guarantee that the input will not include the delimiter, you can do this:

country_list := 'USA,CHINA,HK,JPN';

input := 'JPN'; -- will be found
IF INSTR(',' || country_list || ','
        ,',' || input || ',') > 0 THEN
   --found
ELSE
   --not found
END IF;

input := 'HINA'; --will not be found
IF INSTR(',' || country_list || ','
        ,',' || input || ',') > 0 THEN
   --found
ELSE
   --not found
END IF;

---

If you only want to check a few countries, you can write the list into the if-Statement:

set serveroutput on;

declare 
    input varchar2(10) := 'JPN';
begin
    if input in ('USA', 'CHINA', 'HK' , 'JPN') then
        DBMS_OUTPUT.PUT_LINE('It is Inside');
    else
        DBMS_OUTPUT.PUT_LINE('It is not Inside');
    end if;
end;

If you want to check the existence of a specific value within a larger number of values, you maybe want to use a table:

set serveroutput on;

declare 
    type country_list is table of varchar2(10);
    countries country_list := null;
    input varchar2(10) := 'JPN';
    inside boolean := false;

begin
    countries := country_list('USA', 'CHINA', 'HK' , 'JPN');
    for i in countries.first .. countries.last loop
        if input = countries(i) then
            inside := true;
            exit;
        end if;
    end loop;

    if inside then
        DBMS_OUTPUT.PUT_LINE('It is Inside');
    else
        DBMS_OUTPUT.PUT_LINE('It is not Inside');
    end if;

end;

Another possibility would be to use the MULTISET instruction:

set serveroutput on;

declare 
    type table_varchar IS TABLE OF VARCHAR2(5);
    countries table_varchar := null;
    country_input table_varchar;
    countries_intersect table_varchar;

begin
    country_input := table_varchar('JPN');
    -- Simulate a query which delivers a lot of countries
    select c.country bulk collect into countries from (
        select 'USA' as country from dual union all
        select 'CHINA' as country from dual union all
        select 'HK' as country from dual union all
        select 'JPN' as country from dual
    ) c;
    countries_intersect := country_input MULTISET INTERSECT DISTINCT countries;
    if countries_intersect.count >= 1 then
        DBMS_OUTPUT.PUT_LINE('It is Inside');
    else
        DBMS_OUTPUT.PUT_LINE('It is not Inside');
    end if;

end;

For very large amounts of data, you may not want to use DISTINCT for the MULTISET INTERSECT because this can lead to very long response time.

---

Alternatively, you may define the list of the country codes as a collection, and check with the operator MEMBER OF:

declare
    type tokenList is table of varchar2 (8);  
    countrylist constant tokenList := tokenList ('USA','CHINA','HK','JPN');
    input varchar2 (8) := 'JPN';
begin
    if (input member of countrylist) then
         dbms_output.put_line ('It is Inside');
    else       
         dbms_output.put_line ('It is not  Inside');
    end if;
end;
/

It is Inside

---