394

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

MPelletier
  • 16,256
  • 15
  • 86
  • 137
F.P
  • 17,421
  • 34
  • 123
  • 189
  • Have a look at my answer https://stackoverflow.com/a/74220539/12930883, it will solve your issue. – RED-ONE Oct 28 '22 at 08:28

5 Answers5

411

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%'
Fenton
  • 241,084
  • 71
  • 387
  • 401
  • 8
    is this the only available syntax? – shaffooo Apr 05 '15 at 15:38
  • Just a note: the last query also works in oracle(11g) – Abrar Feb 02 '16 at 15:51
  • I was looking for regex example inside the like but this will do for now ! – Pini Cheyni Mar 24 '16 at 09:29
  • You could use build dynamic SQL statement, stacking the OR's. To improve security, should use parameter binding https://stackoverflow.com/questions/19943202/how-to-late-parameter-bind-to-dynamic-sql-statement-in-a-stored-procedure – oglester Nov 30 '17 at 14:21
  • Last query will not work if IN clause statement returns indefinite number of values. – Prateek Gupta Jan 22 '19 at 12:30
  • Hey, so I know it's been 4063 days since you posted this answer but here's my two cents. This SELECT * FROM table WHERE column IN (1, 2, 3) Is not like this SELECT * FROM table WHERE column = 1 OR column = 2 OR column = 3 Because if you do this: SELECT * FROM table Where column1 = value1 AND column2 = value 2 or column2 = value 22 .. etc Does not give the same results as this SELECT * FROM table WHERE column1 = value1 and column2 IN (1, 2, 3). – Pwner Jan 22 '21 at 10:09
  • So, if we used LIKE we would have to repeat "column LIKE ... or column LIKE...". I wonder anyways to make it short. – Trần Hoàng Kim Long May 27 '21 at 05:14
  • @Fenton hi, this query is working but I have thousands of data that need to check in like, so there will too many " OR LIKE" in query. So is there any query that can combine all "OR LIKE". I have tried some queries but not working. – pratik vekariya Oct 23 '21 at 07:26
  • Thanks! So, we can not combine a bunch of LIKE statments as LIKE (patt1, patt2, ...etc)? – Avv Mar 30 '22 at 12:50
316

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.

Community
  • 1
  • 1
lloydz1
  • 3,209
  • 1
  • 12
  • 3
  • 13
    @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. – Riz Dec 16 '15 at 18:06
  • 21
    Does not accurately address the question. This works with a standard column from another table, not a manually curated list of words. – Dale Kube Aug 18 '17 at 14:15
  • anyway to flag which value it is like? – StelioK Dec 14 '17 at 21:14
  • Doesn't address the question ! – Shiko Sep 12 '18 at 11:28
  • 5
    This can get duplicate results if col1 match the condition for diferent like values – FRL Oct 01 '18 at 10:52
  • 7
    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 – Gerasimos R Nov 14 '18 at 11:44
  • Doesn't address question exactly, however you could try substrings of `Tbla.Col2`. – openwonk Feb 08 '19 at 01:25
  • 4
    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. – Jamie Marshall Feb 12 '19 at 23:04
  • 1
    worked like a charm! For postgresql folks , use this :- (on er.company_name ilike '%'||db."Company"||'%') – Pravin May 31 '19 at 12:16
  • Try Tblb.col1 Like '%'||Tbla.Col2||'%' if "+" doesn't work for you – kennyut Jul 01 '19 at 15:41
  • This link seems to have the correct answer https://dba.stackexchange.com/questions/170579/select-multiple-values-in-like-operator – Ram Venkat Nov 19 '19 at 05:35
  • This solution was half the solution to my problem. The other half was [link](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows). So, in a list of values separated by a character, a simple solution is this: `SELECT * FROM table INNER JOIN STRING_SPLIT(ISNULL('Text,Link,Hello,World',''), ',') t2 ON column Like '%' + isnull(t2.value,column)+'%'` – JS5 Feb 27 '20 at 20:53
  • @JamieMarshall can you post a complete, trivial example? – Evan May 11 '20 at 23:16
  • 1
    @Evan, try this, but I haven't tested it: `SELECT Tbla.* FROM Tbla INNER JOIN ( SELECT Col1 FROM VALUE ('%patern1%', 'pat%ern%2') AS TableAlias(Col1) ) AS Tblb ON Tblb.col1 Like '%'+Tbla.Col2+'%'` – Jamie Marshall May 28 '20 at 16:21
  • The inner join with the literal table approach here is a good solution. I have a SQLite DB in front of me, so please translate to SQL Server or your db of choice: `select distinct n.* from notes n inner join(values ('git'), ('golang'), ('ruby')) v on n.tag like '%' || v.column1 || '%';` – Rohanthewiz Aug 07 '21 at 08:02
  • This is an excellent solution. Although some are complaining (I don't know why) that it doesn't answer the OP, I believe it does. When I searched on this solution, I used keywords IN and LIKE which took me to this thread. The table-based solution combines the concept of using LIKE on a list of values. Whether that list uses IN, a hard-coded list of OR statements or the far more elegant and dynamic table-based solution should not matter. This solution steered me away from using LIKE and IN into a far better table-based solution. – rickj_65 Sep 07 '22 at 14:02
135

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
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • @astander: That's what I thought of. – shahkalpesh Dec 08 '09 at 08:11
  • 12
    This is the closest to the spirit of the question, so +1 – Code Jockey Apr 22 '14 at 13:05
  • Yes this is the answer that i was looking for Thank you – Wahid Bitar May 29 '15 at 17:05
  • 12
    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. – Dave Sexton Nov 16 '15 at 09:55
  • 4
    How is this better, faster or simpler than using multiple LIKE conditions connected with OR? – csar Jun 15 '20 at 07:03
24

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?

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
7

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

Andrew Lygin
  • 6,077
  • 1
  • 32
  • 37