plsql multiple case, regex matching

Question

I wrote a search tool using Oracle Apex 5 which has 2 specific filter/search fields. I am attempting to search one or both, but I am not getting the desired result. Let's say I have Name Surname columns in my table and use Name Surname search field. The data will therefore look like this:

Name    | Surname
--------------------
Phil    | Anselmo
Max     | Cavalera
Maynard | Keenan
Kurt    | Cobain
David   | Gilmour

My Search fields in Apex will be:

:P1_NAME
:P1_SURNAME

I want match either of the following criteria:

regexp_like(NAME, :P1_NAME, 'i') and regexp_like(SURNAME, :P1_SURNAME, 'i')

or

:P1_NAME is null and regexp_like(SURNAME, :P1_SURNAME, 'i')

or

:P1_SURNAME is null and regexp_like(NAME, :P1_NAME, 'i')

But I am unsure of how to incorporate it into a single select? Here is my latest select. I tried quite a few others though.

select name, surname, from leads 
where :P1_SURNAME is null and regexp_like(NAME, :P1_NAME, 'i')
or :P1_NAME is null and regexp_like(SURNAME, :P1_SURNAME, 'i')
or regexp_like(NAME, :P1_NAME, 'i') and regexp_like(SURNAME, :P1_SURNAME, 'i')

Answer 1

Your select seems fine if you add braces for grouping conditions:

select name, surname, from leads 
    where (:P1_SURNAME is null and regexp_like(NAME, :P1_NAME, 'i'))
    or (:P1_NAME is null and regexp_like(SURNAME, :P1_SURNAME, 'i'))
    or (regexp_like(NAME, :P1_NAME, 'i') and regexp_like(SURNAME, :P1_SURNAME, 'i'))

Answer 2

You just need to put brackets around the conditions inside each or, i.e.:

select name, surname
from   leads 
where  (:P1_SURNAME is null and regexp_like(NAME, :P1_NAME, 'i'))
or     (:P1_NAME is null and regexp_like(SURNAME, :P1_SURNAME, 'i'))
or     (regexp_like(NAME, :P1_NAME, 'i') and regexp_like(SURNAME, :P1_SURNAME, 'i'))

That means at least one set of those conditions must be true, and within each condition, both sub-conditions must be true to make that condition true.

Answer 3

Try this query:

select name, surname from leads
where 
instr( upper(:P1_SURNAME), upper(SURNAME) ) + instr( upper(:P1_NAME), upper(NAME) ) > 0