Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-23
5
Medium Priority
?
536 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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.​
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

610 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