Combine the functionality of IN and LIKE in a WHERE clause

Question

Combine the functionality of IN and LIKE in a WHERE clause

mtlsql

SSCommitted

Points: 1528
More actions
May 8, 2015 at 10:33 am

#318081

I would like to be able to combine the functionality of IN and LIKE in a WHERE clause. Although the simple AdventureWorks2012 example below illustrates the concept with 3 search criteria, the real-world example I need to apply the concept to has a couple dozen. This returns 50 rows, but requires multiple OR ... LIKE functions:
SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name LIKE '% sports %'
OR c.Name LIKE '% exercise %'
OR c.Name LIKE '%toy%'
What I would like to do is something like this, which doesn't work:
SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')
I could load up a cursor and loop through it, but the syntax is more cumbersome than the multiple LIKE statements, not to mention most SQL programmers are horrified at the mention of the abominable word 'cursor' for performance reasons.
Does anyone have a more elegant solution to this problem?
Thank you!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) What you have works fine. You can build it dynamically if you have the words in your app too (guarding against SQL Injection obviously).

2) You cannot do what you want.

3) I do have a GREAT solution for you - Full Text Search!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Kevin is right, you cannot combine the in and like items as you've done it.

Full text might help, but you'll still be building a string with multiple and statements for your CONTAINS (or other predicate) statement. However, performance is much better with FTS.

I might suggest you read this as well: http://www.sommarskog.se/dyn-search.html

mtlsql SSCommitted Points: 1528 More actions · Answer 3

Unfortunately, I am a developer working with a 3rd party ERP system rigorously controlled by DBA's. In other words, turning on full text search is not gonna happen...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

jwiseh (5/8/2015)
Unfortunately, I am a developer working with a 3rd party ERP system rigorously controlled by DBA's. In other words, turning on full text search is not gonna happen...

No faith. Have you even tried sitting down and discussing with them what you are trying to accomplish?

Nevyn SSChampion Points: 14210 More actions · Answer 5

I mostly agree with the above.

You can't do an IN and LIKE with that Syntax.

If you could, it would not really be better than the OR solution, as neither is going to use indexes well

Full text search or dynamic SQL could help

But one thing to note is that you CAN use LIKE in a join condition. I wouldn't recommend it, because as stated above, its not really better than using or, not really all that elegant. So you could do something like:

SELECT DISTINCT c.Name

FROM Sales.Store c

WHERE EXISTS (SELECT 1

FROM (VALUES ('% sports %'),('% exercise %'),('%toy%')) l(c)

WHERE c.Name LIKE l.c

)

Or have a table that holds the string profiles and use that instead of the values collection

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

How about...

WITH containsString AS (SELECT string FROM (VALUES ('% sports %'),('% exercise %'),('%toy%')) t(string))

SELECT DISTINCT c.Name

FROM Sales.Store c

JOIN containsString cs ON c.Name LIKE cs.string

Quick edit: Did not see Nevyn or Lynn's post when posting my solution. As is the case with Nevyn's solution - this will be more elegant but an index scan is guaranteed. As everyone else said, FULLTEXT is the way to go. Lastly, what Nevyn posted will perform better.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mtlsql SSCommitted Points: 1528 More actions · Answer 7

Thanks Nevyn! That's the solution I was looking for! I did a little more research based on what you showed me, and sure enough, the information is buried on the MSDN site if you know where to look, like Example C near the bottom of the page here:

https://msdn.microsoft.com/en-us/library/dd776382%28v=sql.105%29.aspx

Thanks for helping to point me in the right direction!

EdSD410 Grasshopper Points: 17 More actions · Answer 8

Hi people,

I know it's been a while, but I just came up with a solution to the combination on IN and LIKE:

I had a situation where I wanted to check for one field in a temporary table and test into a full second table with 250+ captured rows. This is what I came up with after a while and it worked.

select * from Table1 where Table1.Field1 in ( select Table1.Field1 from Table2 where Table2.Field2 like concat('%',Table1.Field1,'%') ) then 'Victory!!' else 'Try Again.' end