425

Is it possible to combine LIKE and IN in a SQL Server-Query?

So, that this query

SELECT * FROM table WHERE column LIKE IN ('Text%', 'Link%', 'Hello%', '%World%')

Finds any of these possible matches:

Text, Textasd, Text hello, Link2, Linkomg, HelloWorld, ThatWorldBusiness

etc...

Improve this question
1

5 Answers 5

Reset to default
450

Effectively, the IN statement creates a series of OR statements... so

SELECT * FROM table WHERE column IN (1, 2, 3)

Is effectively

SELECT * FROM table WHERE column = 1 OR column = 2 OR column = 3

And sadly, that is the route you'll have to take with your LIKE statements

SELECT * FROM table
WHERE column LIKE 'Text%' OR column LIKE 'Hello%' OR column LIKE 'That%'
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

is this the only available syntax?
Just a note: the last query also works in oracle(11g)
I was looking for regex example inside the like but this will do for now !
You could use build dynamic SQL statement, stacking the OR's. To improve security, should use parameter binding stackoverflow.com/questions/19943202/…
So, if we used LIKE we would have to repeat "column LIKE ... or column LIKE...". I wonder anyways to make it short.
|
329

I know this is old but I got a kind of working solution 

SELECT Tbla.* FROM Tbla
INNER JOIN Tblb ON
Tblb.col1 Like '%'+Tbla.Col2+'%'

You can expand it further with your where clause etc. I only answered this because this is what I was looking for and I had to figure out a way of doing it.

Improve this answer

16 Comments

@lloydz1 this isn't the original question though which has a column of values created on the fly. You are doing this on two tables.
Does not accurately address the question. This works with a standard column from another table, not a manually curated list of words.
This can get duplicate results if col1 match the condition for diferent like values
Of course it answers the question - you can do with a table everything you can do with a list, but not vice versa hence THIS is the general solution
I agree with @GerasimosR. Its trivial to use to an in line table statement with a list (SELECT * FROM VALUE ('%patern1%', pat%ern%2') AS TableAlias(Col1)) instead of a table.
|
143

One other option would be to use something like this

SELECT  * 
FROM    table t INNER JOIN
        (
            SELECT  'Text%' Col
            UNION SELECT 'Link%'
            UNION SELECT 'Hello%'
            UNION SELECT '%World%'
        ) List ON t.COLUMN LIKE List.Col
Improve this answer

5 Comments

@astander: That's what I thought of.
This is the closest to the spirit of the question, so +1
Yes this is the answer that i was looking for Thank you
The problem with this solution is if the text column contains text that would find more than one match. For example if your text was 'Hello World' it would find two matches and create an extra row in the results. The first line should be SELECT DISTINCT t.* to avoid this happening.
How is this better, faster or simpler than using multiple LIKE conditions connected with OR?
31

No, you will have to use OR to combine your LIKE statements:

SELECT 
   * 
FROM 
   table
WHERE 
   column LIKE 'Text%' OR 
   column LIKE 'Link%' OR 
   column LIKE 'Hello%' OR
   column LIKE '%World%'

Have you looked at Full-Text Search?

Improve this answer

Comments

8

No, MSSQL doesn't allow such queries. You should use col LIKE '...' OR col LIKE '...' etc.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.