Search code examples
sqlsql-servercsvfind-in-set

SQL Server 2014 equivalent to mysql's find_in_set()

I'm working with a database that has a locations table such as:

locationID | locationHierarchy
1          | 0
2          | 1
3          | 1,2
4          | 1
5          | 1,4
6          | 1,4,5

which makes a tree like this

1
--2
----3
--4
----5
------6

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.

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).

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.

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.

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.

I would imagine searching the locationHierarchy string for locationID% or %,{locationid},% would work but be pretty slow.

Solution

  • I think you want like -- in either database. Something like this:

    select l.*
from locations l
where l.locationHierarchy like @LocationHierarchy + ',%';

    If you want the original location included, then one method is:

    select l.*
from locations l
where l.locationHierarchy + ',' like @LocationHierarchy + ',%';

    I should also note that SQL Server has proper support for recursive queries, so it has other options for hierarchies apart from hierarchy trees (which are still a very reasonable solution).