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