TSQL Finding Records Based on Value in String

Question

I am working with an employee database where one of the fields is the hierarchy which shows the manager ID's of those they report to, their directs report to etc.

The structure for this column is as follows:

123|456|789|135|246|790

The employees manager would be 790, their manager 246 etc, all the way up the chain.

I am trying to find out a way to search for a record where X Manager is in an employees hierarchy.

For example: SELECT FROM employees WHERE '135' IN HierachyColumn

I am unable to use XML due to this being on a linked server which doesn't allow for xml in distributed queries. I say this because I would have wrapped each value in XML and then used .exist to check its presence.

The data it self that contains this string is within a VIEW so I can manipulate it a little bit based on suggestions but I am drawing a blank on a good approach.

Are there any other options for searching based on this string?

John Cappelletti · Answer 1 · 2016-12-21T02:55:33.703

Assuming (at the end of the day) your query, will be non-sargable.

In cases like this, I tend to opt for CharIndex() which may actually give you a performance boost http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex

Declare @Find varchar(25)='|451|'   -- Note I pre-piped the Find

Select * 
 From  employees
 Where CharIndex(@Find,'|'+HierarchyColumn+'|')>0

Full disclosure: Some time ago, I ran a series of tests. My results were not as dramatic as listed in the article, but they were notable.

Alan Burstein · Accepted Answer · 2016-12-20T23:21:20.563

0

This should do the trick:

-- sample data
DECLARE @employees TABLE(HierarchyColumn varchar(100))
INSERT @employees 
VALUES ('123|456|789|135|246|790'), ('144|451|689|822'), ('144|451|689|822|990|999');

-- your search string
DECLARE @searchString int = 451;

-- solution
SELECT * 
FROM @employees 
WHERE HierarchyColumn LIKE CONCAT('%|',@searchstring,'|%')
OR HierarchyColumn LIKE CONCAT(@searchstring,'|%')
OR HierarchyColumn LIKE CONCAT('%|',@searchstring);

Results:

HierarchyColumn
---------------------------
144|451|689|822
144|451|689|822|990|999

Alternatively, you could do this which will get the same results:

-- sample data
DECLARE @employees TABLE(HierarchyColumn varchar(100))
INSERT @employees 
VALUES ('123|456|789|135|246|790'), ('144|451|689|822'), ('144|451|689|822|990|999');

-- your search string
DECLARE @searchString int = 451;

-- solution
SELECT HierarchyColumn 
FROM @employees 
CROSS JOIN 
(
  VALUES (CONCAT('%|',@searchstring,'|%')),
         (CONCAT(@searchstring,'|%')),
         (CONCAT('%|',@searchstring))
) p(pattern)
WHERE HierarchyColumn LIKE pattern;

edited Dec 20 '16 at 23:21

answered Dec 20 '16 at 23:05

Alan Burstein

7,770
1
15
18

Any fancy way to turn this into a single LIKE statement that will catch all 3 scenarios ? I know its a lot to ask, just curious – SBB Dec 20 '16 at 23:07
`'|' + HierarchyColumn + '|' like '%|' + @searchstring +'|%'`. It's not [SARGABLE](https://en.wikipedia.org/wiki/Sargable) so it can't benefit (much) from indexes. – HABO Dec 20 '16 at 23:16
@HABO - that is correct. You are not going to get an index seek on whichever column the optimizer chooses. If you have a set-based solution that is SARGable I'd love to see it. ;) – Alan Burstein Dec 20 '16 at 23:26
@SBB - I updated my answer to include a way to get the same results – Alan Burstein Dec 20 '16 at 23:26
@HABO - I meant to say on whichever **INDEX** the optimizer chooses. – Alan Burstein Dec 20 '16 at 23:35
If I were to prepend and append a | on the result set, couldn't I just get away with the `'%|',@searchstring,'|%'` ? – SBB Dec 21 '16 at 00:36
@SBB if by, "prepend and append a | on the result set" you mean stuff the value of hierarchyString between two pipes ( | )... Yes, you can do that and it would be a better way to go. – Alan Burstein Dec 21 '16 at 03:24

score 0 · Answer 3 · edited May 23 '17 at 11:46

Another approach, provided that all hierarchy nodes are the same length, would be to modify my solution to this post like so:

-- sample data
DECLARE @employees TABLE(HierarchyColumn varchar(100))
INSERT @employees 
VALUES ('123|456|789|135|246|790'), ('144|451|689|822'), ('144|451|689|822|990|999');

-- your search string
DECLARE @searchString int = 451;

SELECT * 
FROM
(
  SELECT 
    HierarchyColumn,
    itemLevel = ROW_NUMBER() OVER (PARTITION BY e.HierarchyColumn ORDER BY N), 
    item      = SUBSTRING(e.HierarchyColumn, ((N*4)-3), 3)
  FROM @employees e
  CROSS APPLY
  (
    SELECT TOP ((LEN(e.HierarchyColumn)/4)+1) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) 
    FROM
    (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(x),
    (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(x),
    (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c(x),
    (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(x)
  ) iTally(N)
) x
WHERE item = @searchString;

This will not only return the node, it will also return the node's position level in the hierarchy.

HierarchyColumn           itemLevel item
------------------------- --------- ----
144|451|689|822           2         451
144|451|689|822|990|999   2         451

TSQL Finding Records Based on Value in String

3 Answers3