I used to write my free text search SQL in Apex Standard Reports or as a Pre-Filter for Interactive Reports or -Grids like this, based on a single text field :P1_SEARCH
Demo see: https://apex.oracle.com/pls/apex/f?p=80981:2
select emp.empno
, emp.ename
, emp.job
, mgr.ename manager
, to_char(emp.hiredate,'YYYYMMDD') hiredate
, emp.sal salary
, emp.comm commision
, dep.dname department
from emp
join dept dep
on dep.deptno = emp.deptno
left outer
join emp mgr
on mgr.empno = emp.mgr
where ( instr( upper(emp.ename) , upper(nvl(:P1_SEARCH,emp.ename)) ) > 0
or instr( upper(emp.job) , upper(nvl(:P1_SEARCH,emp.job)) ) > 0
or instr( upper(mgr.ename) , upper(nvl(:P1_SEARCH,mgr.ename)) ) > 0
or instr( to_char(emp.hiredate,'YYYYMMDD') , nvl(:P1_SEARCH, to_char(emp.hiredate,'YYYYMMDD')) ) > 0
or instr( upper(dep.dname) , upper(nvl(:P1_SEARCH,dep.dname)) ) > 0
)
Having many fields to search on, the readability of the code becomes less and less, specially due to the upper- and nvl-function. Until I came across the CASE WHEN clause, I found the code better to read:
select emp.empno
, emp.ename
, emp.job
, mgr.ename manager
, to_char(emp.hiredate,'YYYYMMDD') hiredate
, emp.sal salary
, emp.comm commision
, dep.dname department
from emp
join dept dep
on dep.deptno = emp.deptno
left outer
join emp mgr
on mgr.empno = emp.mgr
where 1 = case
when :P1_SEARCH is null
then 1
when regexp_like (emp.ename, :P1_SEARCH, 'i')
then 1
when regexp_like (emp.job, :P1_SEARCH, 'i')
then 1
when regexp_like (mgr.ename, :P1_SEARCH, 'i')
then 1
when regexp_like (to_char(emp.hiredate,'YYYYMMDD'), :P1_SEARCH)
then 1
when regexp_like (dep.dname, :P1_SEARCH, 'i')
then 1
else 0
end