Monday, February 21, 2011

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

I define a list a string which contains different country codes ( for example , USA ,CHINA ,HK ,JPN) How can I check that if a input variable equal to one of the country of the country list in pl/sql . I use the following code to test it but fail, how can I revise it?

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;

From stackoverflow
  • 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;
    

0 comments:

Post a Comment