[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using LIKE in an IN clause - SQL

Posted on 2005-04-25
8
Medium Priority
?
294 Views
Last Modified: 2010-03-19
Hi guys/Gals

Is it possible to use a LIKE clause within an IN clause of an SQL statement, for example:

...WHERE somefield IN (Like '%LTD%', Like '%PLC%')

any idea's for using this in SQL server?

0
Comment
Question by:apresto
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1200 total points
ID: 13860694
Sorry, don't think that's possible.

I think you have to do this:

WHERE somefield LIKE '%LTD%' OR somefield LIKE '%PLC%' [OR ...]
0
 
LVL 23

Author Comment

by:apresto
ID: 13860744
Thanks Scott

yeh, i tried 'like' but i wondered if it was possible to use a Like withing an IN, or is that a job for CASE?

Is there any other way to do what im trying to other than straight likes?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13860755
Not that I know of
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

Author Comment

by:apresto
ID: 13860770
ok cool, cheers

Im gonna leave the question open for abit incase someone else knows of a way (doubltful), but why not :o)

thanks again
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 400 total points
ID: 13860781
>> Is there any other way to do what im trying to other than straight likes? <<

You can use charindex as follows:

WHERE CHARINDEX('LTD', somefield) > 0 OR
           CHARINDEX('PLC', somefield) > 0

As to which one is faster, I am not really sure.  Both of them will not use any index anyway.
0
 
LVL 14

Assisted Solution

by:huji
huji earned 400 total points
ID: 13865221
Hi apresto!
As far as I understand SQL, the usage of IN is like this:
SELECT * FROM tableName WHERE colName IN (SELECT colName2 FROM tableName2)

which can be shown as:
................. WHERE .... IN (SQL statement results)

So you see, a series of results generated by SQL can be used after IN. What you are trying to put after IN, is not a series of SQL results. Again as far as I undestand this, such kind of series must be an array of objects (actaully an array of records), but what you give with LIKE is not an array of objects, but an array of statements!

I want to conclude that, "IN" is not developed for that reason. So we have to use other ways. One good method  is to use some ORs, just as ScottPletcher said in his first post. But if you are not going to create your SQL statement "dynamically", then the number of your ORs will be fixed, which can be a disadvantage (i.e. if you wanted to have varying number of items which should be after your IN idea.)

So there is another approach to this case too: You can generate the sql command dynamically! For example, you can get the results of LIKE statements, count all of them, and then generate a SQL statement with enough ORs.

Wish I can help
Huji
0
 
LVL 23

Author Comment

by:apresto
ID: 13887905
Thanks guys, I didnt think it was possible, but i just went with OR's in the end

Thanks for your contributions

Apresto
0
 
LVL 14

Expert Comment

by:huji
ID: 13889306
Glad to be of assistance.
Huji
0

Featured Post

Industry Leaders: 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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 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