Combination of 'LIKE' and 'IN' using t-sql

You don't have a lot of choices here.

SELECT * FROM Street Where StreetName LIKE '% Main Street' OR StreetName LIKE 'foo %'

If this is part of an existing, more complicated query (which is the impression I'm getting), you could create a table value function that does the checking for you.

SELECT * FROM Street Where StreetName IN (dbo.FindStreetNameFunction('% Main Street|foo %'))

I'd recommend using the simplest solution (the first). If this is nested inside a larger, more complicated query, post it and we'll take a look.

I had a similar conundrum but due to only needing to match the start of a string, I changed my 'like' to SUBSTRING as such:

SELECT *  
FROM codes  
WHERE SUBSTRING(code, 1, 12) IN ('012316963429', '012315667849')  

What I did when solving a similar problem was:

SELECT DISTINCT S.* 
FROM Street AS S
JOIN (SELECT value FROM String_Split('% Main Street,foo %', N',')) T 
    ON S.StreetName LIKE T.value;

Which is functionally similar to Martin's answer but a more direct answer to the question.

Note: DISTINCT is used because you might get multiple matches for a single row.

You can resort to Dynamic SQL and wrapping up all in a stored procedure.

If you get the LIKE IN param in a string as tokens with a certain separator, like

'% Main Street,foo %,Another%Street'

first you need to create a function that receives a list of LIKE "tokens" and returns a table of them.

CREATE FUNCTION [dbo].[SplitList]
(
  @list nvarchar(MAX),
  @delim nvarchar(5)
)  
RETURNS @splitTable table 
(       
  value nvarchar(50)
) 
AS BEGIN
  While (Charindex(@delim, @list)>0) Begin 
    Insert Into @splitTable (value)
      Select ltrim(rtrim(Substring(@list, 1, Charindex(@delim, @list)-1))) 
    Set @list = Substring(@list, Charindex(@delim, @list)+len(@delim), len(@list))
  End     
  Insert Into @splitTable (value) Select ltrim(rtrim(@list))
  Return
END 

Then in the SP you have the following code

declare 
  @sql nvarchar(MAX),
  @subWhere nvarchar(MAX)
  @params nvarchar(MAX)

-- prepare the where sub-clause to cover LIKE IN (...)
-- it will actually generate where sub clause StreetName Like option1 or StreetName Like option2 or ...   
set @subWhere = STUFF(
  (
    --(**)
    SELECT ' OR StreetName like ''' + value + '''' FROM SplitList('% Main Street,foo %,Another%Street', ',') 
      FOR XML PATH('')
  ), 1, 4, '')

-- create the dynamic SQL
set @sql ='select * from [Street]
  where 
    (' + @subWhere + ')
    -- and any additional query params here, if needed, like
    AND StreetMinHouseNumber = @minHouseNumber
    AND StreetNumberOfHouses between (@minNumberOfHouses and @maxNumberOfHouses)'

set @params = ' @minHouseNumber nvarchar(5),
  @minNumberOfHouses int,
  @minNumberOfHouses int'     

EXECUTE sp_executesql @sql, @params,    
  @minHouseNumber,
  @minNumberOfHouses,
  @minNumberOfHouses     

Of course, if you have your LIKE IN parameters in another table or you gather it through a query, you can replace that in line (**)

I believe I can clarify what he is looking for, but I don't know the answer. I'll use my situation to demonstrate. I have a table with a column called "Query" that holds SQL queries. These queries sometimes contain table names from one of my databases. I need to find all Query rows that contain table names from a particular database. So, I can use the following code to get the table names:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

I'm trying to use a WHERE IN clause to identify the Query rows that contain the table names I'm interested in:

SELECT *
  FROM [DatasourceQuery]
 WHERE Query IN LIKE
(
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
)

I believe the OP is trying to do something like that.

This is my way:

First create a table function:

create function [splitDelimeter](@str nvarchar(max), @delimeter nvarchar(10)='*')
returns @r table(val nvarchar(max))
as
begin

    declare @x nvarchar(max)=@str
    set @x='<m>'+replace(@x, @delimeter, '</m><m>')+'</m>'

    declare @xx xml=cast(@x as xml)

    insert @r(val)
    SELECT Tbl.Col.value('.', 'nvarchar(max)') id
    FROM @xx.nodes('/m') Tbl(Col)

    return 
end

Then split the search text with your preference delimeter. After that you can do your select with left join as below:

  declare @s nvarchar(max)='% Main Street*foo %'

  select a.* from street a
     left join gen.splitDelimeter(@s, '*') b
        on a.streetname like b.val
  where val is not null