Combine the functionality of IN and LIKE in a WHERE clause

  • 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!

  • 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

  • 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

  • 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...

  • 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?

  • 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

  • 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

  • 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!

  • 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

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

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