CODEWITHSUNDEEP
parametersplsqloracle11gsql-like
zundarz
zundarz

Reputation: 1594

How can I integrate a parameter list into the LIKE clause?

This code works when I list the codes as written in parm_list_a. However, I'd like to implement like as shown in parm_list_b.

What can I do to get this code to work using parm_list_b

set serveroutput on;
declare
parm_list_a varchar2(100) := 'ADAR,CADD';
parm_list_b varchar2(100) := 'A%,BE%'; 

cursor c_ok_counties 
is
with ok_counties as
(select 'ALFA' AS cty_code, 'Alfalfa' as cty_name from dual union all
 select 'ATOK' AS cty_code, 'Atoka'   as cty_name from dual union all
 select 'BEAV' AS cty_code, 'Beaver'  as cty_name from dual union all
 select 'BECK' AS cty_code, 'Beckahm' as cty_name from dual union all
 select 'BLAI' AS cty_code, 'Blaine'  as cty_name from dual union all
 select 'CADD' as cty_code, 'Caddo'   as cty_name from dual)
 select cty_code,
        cty_name
 from  ok_counties  
 where cty_code in 
      (select regexp_substr(
      parm_list_a,   -- Replace with parm_list_b
      '[^,]+',1,LEVEL)
      from dual
      connect by regexp_substr(
      parm_list_a  -- Replace with parm_list_b
      ,'[^,]+',1,LEVEL) is not null);

begin
         for county in c_ok_counties loop
         dbms_output.put_line(county.cty_code || ' ' ||county.cty_name);         
         end loop;
end;

Desired Results

   ALFA Alfalfa
   ATOK Atoka
   BEAV Beaver
   BECK Beckham

Upvotes: 1

Views: 72

Answers (1)

ajmalmhd04
ajmalmhd04

Reputation: 2602

As you have to use multiplie use of LIKE in the IN, You can better code like:

set serveroutput on;
declare
parm_list_a varchar2(100) := 'ADAR,CADD';
parm_list_b varchar2(100) := 'A|BE'; 

cursor c_ok_counties 
is
with ok_counties as
(select 'ALFA' AS cty_code, 'Alfalfa' as cty_name from dual union all
 select 'ATOK' AS cty_code, 'Atoka'   as cty_name from dual union all
 select 'BEAV' AS cty_code, 'Beaver'  as cty_name from dual union all
 select 'BECK' AS cty_code, 'Beckahm' as cty_name from dual union all
 select 'BLAI' AS cty_code, 'Blaine'  as cty_name from dual union all
 select 'CADD' as cty_code, 'Caddo'   as cty_name from dual)
 select cty_code,
        cty_name
 FROM  OK_COUNTIES  
 where regexp_like(cty_code, '^('||parm_list_b||')');

begin
         for county in c_ok_counties loop
         dbms_output.put_line(county.cty_code || ' ' ||county.cty_name);         
         END LOOP;
end;

Upvotes: 1

Related Questions