使用 t-sql 组合“LIKE”和“IN”

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

提问by Panagiotis Lefas

How can I do this kind of selection:

我该如何进行这种选择：

SELECT * 
FROM Street 
WHERE StreetName LIKE IN ('% Main Street', 'foo %')

Please don't tell me that I can use ORbecause these actually comes from a query.

请不要告诉我我可以使用，OR因为这些实际上来自查询。

采纳答案by Martin Smith

There is no combined LIKEand INsyntax but you can use LIKEto JOINonto your query as below.

有没有合并LIKE和IN语法，但你可以使用LIKE到JOIN到您的查询，如下。

;WITH Query(Result) As
(
SELECT '% Main Street' UNION ALL
SELECT 'foo %'
)
SELECT DISTINCT s.* 
FROM Street s
JOIN Query q ON StreetName LIKE q.Result

Or to use your example in the comments

或者在评论中使用您的示例

SELECT DISTINCT s.* 
FROM Street s
JOIN CarStreets cs ON s.StreetName LIKE cs.name + '%'
WHERE cs.Streets = 'offroad'

回答by James Hill

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

我建议使用最简单的解决方案（第一个）。如果它嵌套在一个更大、更复杂的查询中，请将其发布，我们将查看。

回答by bzamfir

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

您可以求助于动态 SQL 并将所有内容包装在存储过程中。

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

如果您将字符串中的 LIKE IN 参数作为具有特定分隔符的标记，例如

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

首先，您需要创建一个接收 LIKE “令牌”列表并返回它们的表的函数。

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

然后在SP中你有以下代码

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 (**)

当然，如果你在另一个表中有你的 LIKE IN 参数或者你通过查询收集它，你可以在行中替换它 (**)

回答by breusshe

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:

我相信我可以澄清他在寻找什么，但我不知道答案。我会用我的情况来证明。我有一个表，其中包含一个名为“查询”的列，其中包含 SQL 查询。这些查询有时包含来自我的数据库之一的表名。我需要从特定数据库中查找包含表名的所有查询行。因此，我可以使用以下代码来获取表名：

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:

我正在尝试使用 WHERE 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.

我相信 OP 正在尝试做这样的事情。

回答by Samuel Surya

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

回答by JLWarlow

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

我有一个类似的难题，但由于只需要匹配字符串的开头，我将“喜欢”更改为SUBSTRING，如下所示：

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