How to filter condition inside select statement where values of row even contains value as "null" but we need to exclude it

Question

I have a case where in 2 input variables I will be passing comma separated email and user id or either or none

now the thing is procedure will have AND condition where if email exists, it should be applying to filter for email column of table ,same for userid

and the situation is email and userid in database have value as "null" as well, so in nvl if we send null then it will return all values even the ones holding holding null as well, then it will take as "where userid is null"

example:

userid.             name
null.                  xyz
abc.                  null
adj.                   kak

example :

procedurename(phone in number, name in varchar2, userid in varchar2, cursor_c out sys_refcursor) is

begin

open cursor_c for
select name, email,mobile,phone, address, department,grade,scale

from employee where 

user_id =nvl(select regexpr_str(userid,'[^,]+',1,level) from dual connect by regexpr_str(userid,'[^,]+',1,level) is not null),user_id))

and name=nvl(select regexpr_str(name,'[^,]+',1,level) from dual connect by regexpr_str(name,'[^,]+',1,level) is not null),name))

so if we pass NULL as parameters, then it will happen that it will even select the rows where " name=null"

which should not be done as it will select those where name is null but we want all those ids except null

how can this be done that if all rows are selected in case of input parameter null, but exclude values having null else it will make condition as where name=null.

'Null' string is different than actual null value. Just try this with null value in parameters instead of try passing them as 'null' and you will get your result. — Ankit Bajpai

Littlefoot Littlefoot · Accepted Answer · 2021-06-28T20:19:56

To me, it looks like

create or replace procedure p_proc 
  (par_user_id in number, par_name in number, cursor_c out sys_refcursor)
is
begin
  open cursor_c for
    select name, email, mobile, phone, address, department, grade, scale
    from employee 
    where (user_id in (select regexp_substr(par_user_id, '[^,]+', 1, level)
                       from dual
                       connect by level <= regexp_count(par_user_id, ',') + 1
                      )
           or par_user_id is null
          )
      and (name in (select regexp_substr(par_name, '[^,]+', 1, level)
                    from dual
                    connect by level <= regexp_count(par_name, ',') + 1
                   )
           or par_name is null
          );
end;

How to filter condition inside select statement where values of row even contains value as "null" but we need to exclude it

1 Answers