How do I validate the filters using substr/regex function and get results from stored procedure in Oracle PLSQL?

Question

I need help in writing a procedure to retrieve the details of the employee when user perform search for either place or county or zip code (all 3 contained in the Emp_Address column by comma and space separated).

Employee table:

Emp_Address column consists ( Place, County (zipcode))

EMP_ID    Emp_Address           
--------------------------------------
1         Leeds, NorthYork  (ls2ph) 
2         London, NorthHam (tl9yh)  
3         Cunniham, Norwalk (tc1f1)     
4         Excel, Shire (cp14)   
5         Bradford, Clarkson (cr123) 

Procedure:

Procedure search_emp (pt_zipCode in  varchar2,
                      pt_address in  varchar2,                       
                      empCursor  out ref cursor)
as
begin
open empCursor for
    select e.emp_id,
           e.emp_address       
    from employee e
    where ( UPPER(REGEXP_SUBSTR(Emp_Address, '\((.+)\)', 1, 1, NULL, 1)) = UPPER (pt_zipCode) or pt_zipCode IS NULL ) -- this is working fine when searched for zipCode directly

--- I'm not sure how to get the results when the User enters (First '2' characters only for place /county (or) entire place (or) entire county) in 'pt_address' input parameter.

end;

Expected output:

a) Search for "No" in 'pt_address' parameter.  Below 3 records should display

EMP_ID     Emp_Address          
--------------------------------------
1          Leeds, NorthYork (ls2ph) 
2          London, NorthHam (tl9yh) 
3          Cunniham, Norwalk (tc1f1)


b) Search "NorthHam" :

EMP_ID    Emp_Address
--------------------------------------
2         London, NorthHam (tl9yh)  


c) Search "Excel"

EMP_ID    Emp_Address
--------------------------------------
4          Excel, Shire (cp14)

Please help.

Answer 1

There's a way simpler option: INSTR.

SQL> create or replace procedure search_emp
  2    (pt_zipcode in varchar2,
  3     pt_address in varchar2,
  4     empcursor out sys_refcursor)
  5  is
  6  begin
  7    open empcursor for
  8      select emp_id, emp_address
  9      from test
 10      where instr(upper(emp_address), upper(pt_zipcode)) > 0
 11         or instr(upper(emp_address), upper(pt_address)) > 0;
 12  end;
 13  /

Procedure created.

Testing:

SQL> var l_rc refcursor
SQL>
SQL> exec search_emp(null, 'No', :l_rc);

PL/SQL procedure successfully completed.

SQL> print l_rc;

    EMP_ID EMP_ADDRESS
---------- --------------------------------------------------
         1 Leeds, NorthYork  (ls2ph)
         2 London, NorthHam (tl9yh)
         3 Cunniham, Norwalk (tc1f1)

SQL> exec search_emp(null, 'NorthHam', :l_rc);

PL/SQL procedure successfully completed.

SQL> print l_rc;

    EMP_ID EMP_ADDRESS
---------- --------------------------------------------------
         2 London, NorthHam (tl9yh)

SQL> exec search_emp(null, 'Excel', :l_rc);

PL/SQL procedure successfully completed.

SQL> print l_rc;

    EMP_ID EMP_ADDRESS
---------- --------------------------------------------------
         4 Excel, Shire (cp14)

SQL>