title: How to search if a list contains values from another list TSQL
body: <p>Ok I have done extensive research into this, and tried many techniques to no avail.</p>
  
  <p>I have a list of Names, I have a list of Locations.  Some Locations have the full Names string contained within them.</p>
  
  <p>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)</p>
  
  <p>ALSO ALL QUERIES WORK INDIVIDUALLY, except the last one where i finally try to combine</p>
  
  <pre><code>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 &gt; 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
  </code></pre>
  
  <p>---------------The part where it comes together</p>
  
  <p>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</p>
  
  <pre><code>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...
  </code></pre>
  
  <p>----------Coworker just brought me this:</p>
  
  <pre><code> select lastname
   from #LastName
   Where charindex(LastName,
   (SELECT STUFF((SELECT ',' + LocationName
    FROM #LocationName
    FOR XML PATH('')) ,1,1,'') AS Txt )
  
    )&gt;1
  </code></pre>
  
  <p>------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</p>
  
  <p>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.</p>
  
  <p>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</p>