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

try this:

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