How to filter a tree?

Question

Transact SQL

There is a table:

declare @tree table
(
  id int,
  ParentID int,
  Name varchar(100)
);

How can it be filtered by Name so that if the found row had parents by ParentID and he pulled them all out?
For example:

insert @tree values
(1,null,'111'),
(2,1,'222'),
(3,1,'333'),
(4,2,'444'),
(5,2,'555'),
(6,4,'666'),
(7,4,'777');

If I'm looking for a name like '%444%', then the lines with id 1 and 2 should also be drawn, since they are his parents (by parentID).

Answer 1

How can it be filtered by Name so that if the found row had parents by ParentID and he pulled them all out?

I suspect that the found row cannot have more than one parent.
The first option that comes to mind is combining the results:

SELECT * FROM @tree AS a WHERE a.Name LIKE '%444%'
UNION
SELECT b.* FROM @tree AS a
INNER JOIN @tree AS b ON b.id = a.ParentID
WHERE a.Name LIKE '%444%'

Updated:
Although no, more than one parent can be. I misunderstood the question. Then like this:

;WITH hierarchy
(
  [level], id, ParentID, Name
)
AS
(
  SELECT 
  0, id, ParentID, Name
  FROM @tree AS a
  WHERE a.name LIKE '%444%'
  UNION ALL
  SELECT
  h.[level] + 1, b.id, b.ParentID, b.Name
  FROM @tree AS b
  INNER JOIN hierarchy AS h ON h.ParentID = b.id
)

SELECT * FROM hierarchy;