title: Using SQL, how do I convert a comma separated list to be used with the IN command?
body: <p>Currently I have a table that has a user_type column and will only show the row if the user matches that user_type. I want to make it so there can be multiple user_types set without duplicating the data and without creating another table. I thought that I could convert the column from an int to varchar and have it be a comma separated list of user type IDs.</p>
  
  <p>So far it has been working great. That is as long as I know what the user_type is beforehand because then I can specifically use that when checking if I should display to the user:</p>
  
  <pre><code>SELECT *
  FROM perm
  WHERE user_type='50'
      OR user_type LIKE '50,%'
      OR user_type LIKE '%,50,%'
      OR user_type LIKE '%,50'
  </code></pre>
  
  <p>The issue comes when I am trying to join tables on the value. When I tried using <code>IN</code>:</p>
  
  <pre><code>SELECT p.*
  FROM perm p
  JOIN [user] u ON u.type IN (p.user_type)
  </code></pre>
  
  <p>I get the error: <code>Conversion failed when converting the varchar value '50,40,30' to data type int.</code> So I decided I would go back to the <code>LIKE</code> method which I have been using above:</p>
  
  <pre><code>SELECT p.*
  FROM perm p
  JOIN [user] u ON (
      u.type LIKE p.user_type
      OR u.type LIKE (p.user_type + ',%')
      OR u.type LIKE ('%,' + p.user_type + ',%')
      OR u.type LIKE ('%,' + p.user_type)
  )
  </code></pre>
  
  <p>This only returns results that have one user type value only. Is there a way to convert a comma separated list to be used with the <code>IN</code> command? Or is there any way to make a dynamic <code>LIKE</code> argument?</p>