如何搜索列表是否包含来自另一个列表TSQL的值

Ok I have done extensive research into this, and tried many techniques to no avail.

好的，我已经对此进行了广泛的研究，并尝试了许多技术无济于事。

I have a list of Names, I have a list of Locations. Some Locations have the full Names string contained within them.

我有一个名单列表，我有一个地点列表。某些位置包含其中包含的完整名称字符串。

I want to pull only the locations which contain the Names somewhere in them, and I do not want to check from a hard-coded list of %names%, needs to be scalable. What i'm working with is below (I don't check for existing temp tables everytime, sry not really a need)

我想只拉出其中包含Names的位置，我不想从％name％的硬编码列表中检查，需要是可扩展的。我正在使用的是下面的内容（我不是每次都检查现有的临时表，不是真的需要）

ALSO ALL QUERIES WORK INDIVIDUALLY, except the last one where i finally try to combine

所有查询都是单独工作的，除了我最后尝试结合的最后一个

IF OBJECT_ID('tempdb..#ProviderList') IS NOT NULL
DROP TABLE #ProviderList

SELECT distinct
Station
,StaffName --first and last concatenated
,lastName
,ProviderID
--,[ProviderRole]
INTO #ProviderList
FROM --Omited--
Where PrimaryProviderSID in (Select distinct
                            PrimaryProviderSID
                        ----
                        Where PrimaryPosition not like '%zz%'
                            AND PrimaryProviderID > 0)
    AND [TerminationDate] is null
AND (InactivationDate is null)




Select LastName 
INTO #LastName
from #ProviderList


/* Failed experiment
Declare @Names as Table(Name varchar(max))
Insert Into @Names Select LastName 
from #ProviderList

--Select* From @Names

Failed experiment
*/






SELECT distinct [Many fields...],
  LocationName
Into #LocationName
  FROM ------
Where LocationName not like '%z%'
    AND ---filtering criteria to make my list

---------------The part where it comes together

---------------它汇集在一起的部分

I want to select Everyting from #LocationName but only the records which contain ANY record from the Names list, they will only contain one or none

我想从#LocationName中选择Everyting，但只选择包含Names列表中任何记录的记录，它们只包含一个或没有

Select *
From #LocationName  
Where #LastName in (LocationName)?
Where '%' + LastName + '%'  ? --tried concatenation, i want a fuzzy search
Where #LocationName contains(LocationName, #LastName)? ---i know syntax is wrong but this has not proven to be the correct technique anyway
Where Exists(Select LastName from #LastName) --made no change but Not Exists returned nothing...

----------Coworker just brought me this:

---------- Coworker刚给我带来了这个：

 select lastname
 from #LastName
 Where charindex(LastName,
 (SELECT STUFF((SELECT ',' + LocationName
  FROM #LocationName
  FOR XML PATH('')) ,1,1,'') AS Txt )

  )>1

------But that is only going to give us a list of names that are contained in the LocationName field, we also want to pull the Location Name

------但这只会给我们一个包含在LocationName字段中的名字列表，我们也想拉出位置名称

I know this is a long one, but i hope I was relatively clear about what needs to be done at least if not how I'm doing it.

我知道这是一个很长的问题，但我希望我相对清楚自己至少应该做些什么而不是我是怎么做的。

I feel like I want to make an array and compare each Name value individually to see if it is contained in the LocationName, then if yes add it to a new list. That would be ideal

我觉得我想创建一个数组并分别比较每个Name值以查看它是否包含在LocationName中，然后如果是，则将其添加到新列表中。那将是理想的

2 个解决方案

#1

Give this a try...

尝试一下......

CREATE TABLE #NAMES (NAME VARCHAR(100))
CREATE TABLE #LOCATIONS (LOCATIONNAME VARCHAR(100))

INSERT INTO #NAMES 
SELECT 'DAVE' UNION
SELECT 'BOB' UNION 
SELECT 'JIMMY'

INSERT INTO #LOCATIONS 
SELECT 'TODDS BOAT' UNION
SELECT 'MARYS HOUSE' UNION
SELECT 'DAVES HOTTUB' UNION
SELECT 'JIMMYS CRICKEY CLUB'

SELECT L.*
FROM #LOCATIONS L
CROSS APPLY #NAMES N
WHERE CHARINDEX(N.NAME, L.LOCATIONNAME)>0

#2

Maybe this does deserve a separate answer rather than just a comment to JiggsJedi.

也许这确实应该得到一个单独的答案，而不仅仅是对JiggsJedi的评论。

It can also be done with a correlated subquery. The execution plan on this sample data is the same either way.

它也可以使用相关子查询来完成。此示例数据的执行计划是相同的。

Use the sample data from JiggsJedi.

使用JiggsJedi中的示例数据。

SELECT L.* 
  FROM #LOCATIONS L 
 WHERE EXISTS (SELECT 1 
                 FROM #NAMES N 
                WHERE CHARINDEX(N.NAME, L.LOCATIONNAME) > 0)

#1