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

Question

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;

Answer 1

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;