Case in where clause with column Like condition

Question

I'm using a SQL query inside a function. Now I want to pass the parameter value to the SQL where clause only if the param_value is not empty like below.

select * 
  from cms_tab 
 where case when param_val <> '' then 
         col1 like '%' || param_val || '%' 
       end

How to achieve this?

If the parameter value is not null then I want to fetch records using like (%).

Answer 1

The only safe way to check if a string is "empty" in Oracle is to check if it IS [NOT] NULL; here you find an example of the issues you can find while checking "empty strings" the wrong way.

Also, CASE is not the right choice here; you should better add some boolean logic.

If I understand well, you want to select all the rows if the parameter is "empty" or only the matching rows if the parameter is not "empty"; this could be a way:

create or replace procedure testNull ( param_val IN varchar2) is
    vCount number;
begin
    select count(*)
    into vCount
    from cms_tab
    where param_val is null
       or col1 like '%' || param_val || '%' ;
    dbms_output.put_line('vCount = ' || vCount);
end;    

Test:

create table cms_tab(col1) as (
    select 'xxx ABC yyy' from dual union all
    select 'xxxxxxxxxxxx' from dual
    )

SQL> exec testNull(null);
vCount = 2

PL/SQL procedure successfully completed.

SQL> exec testNull('');
vCount = 2

PL/SQL procedure successfully completed.

SQL> exec testNull('ABC');
vCount = 1

PL/SQL procedure successfully completed.

SQL> exec testNull('ZZZ');
vCount = 0

PL/SQL procedure successfully completed.

Answer 2

try this:

select * from cms_tab 
where 
param_val is null
 or col1 like '%'||param_val||'%'