How to test if a string is inside a list of predinfined list of string in oracle pl/sql

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;