Solved

Using IN and LIKE Operator together Oracle SQL %

Posted on 2008-06-18
3
5,133 Views
Last Modified: 2013-12-12
Hi,

I have a text list to use with IN operator - IN ('a','b') yet i need to compare it to a field that might contain the value of a;b so i want to put wild cards in the IN list??? Doesn't seem to work - ie:

WHERE IN ('%a%' ,  '%b%')

Any other way to achieve this without importing text files to table etc?

Cheers,
Brett
0
Comment
Question by:brettanderson77
  • 2
3 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21811777
Have you tried the LIKE operator:

WHERE colname LIKE '%a%' or colname LIKE '%b%'
0
 

Author Comment

by:brettanderson77
ID: 21811801
Hi,
Problem is it isn't in a table, its values i've copied into a list (a,b etc) from XL.
Hence why i'm trying to use IN ()
Cheers,
Brett
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 21812080
You could try building a stored function which would accept the list as an argument and then loop through each item in the list using a sql statement to "like" the values.  The function could then be called from the WHERE clause of the statement to control which rows are returned.

Not a very elegant or efficient solution though.  I may be missing something that another expert can identify, but I'm not aware of an easy way of doing what you want.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

24 Experts available now in Live!

Get 1:1 Help Now