SQL Server 2014 equivalent to mysql's find_in_set()

Question

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.

If possible, I would suggest changing your data structure. There are several examples of storing hierarchical data. If that's not an option, then your last comment is probably the easiest solution using `like`... — sgeddes, Mar 01 '16 at 14:39
SQL Server has a special data type for this kind of data structure: `hierarchyid` which is probably a better choice then a "CSV column": https://msdn.microsoft.com/en-us/library/bb677173%28v=sql.110%29.aspx — , Mar 01 '16 at 14:45
while SQL Server does have a datatype specifically for this, the employer wants to avoid vendor lock-in at all costs. I'll have to research better hierarchial structures but in the mean time it looks like a series of LIKE statements is going to have to do. — MMDeveloper, Mar 01 '16 at 15:03

score 0 · Accepted Answer · answered Mar 01 '16 at 14:37

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

score 0 · Answer 2 · answered May 09 '18 at 07:45

Finally It worked for me..

SELECT * FROM locations WHERE locationHierarchy like CONCAT(@param,',%%')    OR
                                    o.unitnumber like CONCAT('%%,',@param,',%%') OR
                                    o.unitnumber like CONCAT('%%,',@param)

SQL Server 2014 equivalent to mysql's find_in_set()

2 Answers2

Linked