?
Solved

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

Posted on 2012-03-23
5
Medium Priority
?
531 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 332 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 668 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 668 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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.
Suggested Courses

770 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