title: SQL Server 2014 equivalent to mysql's find_in_set()
body: <p>I'm working with a database that has a locations table such as:</p>
  
  <pre><code>locationID | locationHierarchy
  1          | 0
  2          | 1
  3          | 1,2
  4          | 1
  5          | 1,4
  6          | 1,4,5
  </code></pre>
  
  <p>which makes a tree like this</p>
  
  <pre><code>1
  --2
  ----3
  --4
  ----5
  ------6
  </code></pre>
  
  <p>where locationHierarchy is a csv string of the locationIDs of all its ancesters (think of a hierarchy tree). This makes it easy to determine the hierarchy when working toward the top of the tree given a starting locationID.</p>
  
  <p>Now I need to write code to start with an ancestor and recursively find all descendants. MySQL has a function called 'find_in_set' which easily parses a csv string to look for a value. It's nice because I can just say "find in set the value 4" which would give all locations that are descendants of locationID of 4 (including 4 itself).</p>
  
  <p>Unfortunately this is being developed on SQL Server 2014 and it has no such function. The CSV string is a variable length (virtually unlimited levels allowed) and I need a way to find all ancestors of a location.</p>
  
  <p>A lot of what I've found on the internet to mimic the find_in_set function into SQL Server assumes a fixed depth of hierarchy such as 4 levels maximum) which wouldn't work for me.</p>
  
  <p>Does anyone have a stored procedure or anything that I could integrate into a query? I'd really rather not have to pull all records from this table to use code to individually parse the CSV string.</p>
  
  <p>I would imagine searching the locationHierarchy string for locationID% or %,{locationid},% would work but be pretty slow.</p>