• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

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

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
bcarlis
Asked:
bcarlis
  • 2
  • 2
3 Solutions
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
You also need to likely account for case sensitivity.  This can further have an impact on any potential index use.
0
 
bcarlisAuthor Commented:
thank you both!
0
 
bcarlisAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now