title: mssql compare two multiple values strings in where clause
body: <p>I want to compare two multiple value strings with each other to see if one of the values exists in the other string. </p>
  
  <p>I have a table with a nvarchar row with pipe separated values, e.g. </p>
  
  <blockquote>
    <p>'value1|value2|value3'</p>
  </blockquote>
  
  <p>I also have an nvarchar variable with a comma separated string, e.g. </p>
  
  <blockquote>
    <p>'value2,value3'</p>
  </blockquote>
  
  <p>until now the column in the table had one value, I used a table function to spit the string in the variable and used the IN clause to see if the value was in the generated table. e.g.</p>
  
  <pre><code>select * from table1
      WHERE column in (select val from dbo.split(@variable,','))
  </code></pre>
  
  <p>this won't work if the column also contains more values. </p>
  
  <pre><code>select * from table1    
      WHERE (select val from dbo.split(column,'|')) in (select val from dbo.split(@variable,','))
  </code></pre>
  
  <p>here it tries to compare 2 generated tables with each other which fails. I have tried this using joins, but can't find a way to properly do this. I'm using MSSQL 2008R2</p>