Solved

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

Posted on 2012-03-23
5
513 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

758 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

22 Experts available now in Live!

Get 1:1 Help Now