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')

Sianur · Accepted Answer · 2018-06-26 13:09:04Z

2

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'))

answered Jun 26, 2018 at 13:09

Sianur

6496 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Boneist · Accepted Answer · 2018-06-26 13:13:54Z

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.

answered Jun 26, 2018 at 13:13

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Comments

Paweł Prusinowski · Accepted Answer · 2018-06-26 13:09:33Z

0

Try this query:

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

answered Jun 26, 2018 at 13:09

Paweł Prusinowski

4163 silver badges6 bronze badges

2 Comments

user8956060 Over a year ago

Does not seem like my regex search will function any longer?

Paweł Prusinowski Over a year ago

It will work. But instr solution looks more clearly and might run faster.

Collectives™ on Stack Overflow

plsql multiple case, regex matching

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related