• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • 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
BILL Carlisle
Asked:
BILL Carlisle
  • 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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
thank you both!
0
 
BILL CarlisleAPEX DeveloperAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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