I might not be realizing the problem that Sean is talking about, but here's an example on how to do it. Be sure to understand what's going on before using it. And test it over a large amount of records as you might find that there can be performance problems.

DECLARE @Value int = 5;

WITH rCTE AS(

SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID AS varchar(10)) AS varchar(8000)) AS String,

ParentID,

ChildID

FROM Hierarchy

WHERE ParentID = @Value

UNION ALL

SELECT CAST( r.String + '/' + CAST( h.ChildID AS varchar(10)) AS varchar(8000)) As String,

h.ParentID,

h.ChildID

FROM Hierarchy h

JOIN rCTE r ON h.ParentID = r.ChildID

)

SELECT String

FROM rCTE r

WHERE NOT EXISTS( SELECT *

FROM rCTE x

WHERE x.String LIKE r.String + '%' --Contains the string

AND x.String > r.String) --And is larger than the string

ORDER BY String