Solved

SQL: Should I use Like or Instr or ???

Posted on 2012-03-23
5
516 Views
Last Modified: 2012-03-23
Hi,
I usually use the first CASE below but was wondering which is best? or some other way ?
      CASE
      WHEN nvl(:P130_SEARCH_TERM,'NULL')='NULL' THEN 1
      WHEN INSTR(C.DESCRIPTION, :P130_SEARCH_TERM) > 0
        OR INSTR(C.NAME,        :P130_SEARCH_TERM) > 0 THEN 1
      ELSE 0
      END = 1
      
      CASE
      WHEN nvl(:P130_SEARCH_TERM,'NULL')='NULL' THEN 1
      WHEN C.DESCRIPTION like '%'||:P130_SEARCH_TERM||'%'
        OR C.NAME        like '%'||:P130_SEARCH_TERM||'%' THEN 1
      ELSE 0
      END = 1
thank you, Bill
0
Comment
Question by:bcarlis
  • 2
  • 2
5 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 83 total points
ID: 37757559
doesn't really matter either way for a middle-string search

LIKE is probably a tiny bit more efficient

if you could change your search to    LIKE :P130_search_term  || '%'   that would be indexable because the it would be leading value in the string.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
ID: 37757571
I agree with the above.  Just wanted to add that items like this can easily be tested to see which one works better.

Create a simple table, load up some data and check out explain plans, performance times, etc...
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
ID: 37757577
You also need to likely account for case sensitivity.  This can further have an impact on any potential index use.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 37757866
thank you both!
0
 
LVL 2

Author Comment

by:bcarlis
ID: 37757876
yes I ended up doing the upper on both side also..
I like using the CASE that way

I will test it out with explain plan
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now