I have a query that takes user input, basically, user can decide if:

1.The filter is an ‘or’ or an ‘and’
2. The value of said filter (using preset existing values on my_table) / :filter_bind
(as an example I’m using columns job_title and Id)

The ‘or’ part is working perfectly so I’m trying to use the same logic into the ‘and’ piece:

select count(*) from my_table
where         :my_or_and_bind = 'and' 
and
(
:filter_bind is null
or
(
instr(':' || regexp_replace(lower(:filter_bind), 's+') || ':', ':' ||    regexp_replace(lower(job_title), 's+') || ':') > 0
and
instr(':' || :filter_bind || ':', ':' || id || ':') > 0
)
)  

When replacing the bind filter for an existing job_title value e.g. ‘vp’ it works, bringing all rows with job_title ‘vp’ but if I add something else to the bind it stops working even if there’s a matching id within the VP results e.g. ‘vp:98’ (job title vp and id #98).
Instead of returning the one row where job title = vp and id = 98 it returns 0 rows.
Any ideas?

Thanks