Select children from hierarchy (/a/b/c/d/) I have to select from database which I did not create and I have this problem:

example schema:

Unit
----
id
HierarchyPath (example: '/42/48/63/84/')
UnitName (example1: "John" example2: "Team1")
typeId

UnitType
--------
id
Name (example1: "Name", example2: "TeamName")


I need to have result like

Team1, John
Team1, Joe
Team1, Johana
Team2, Bob
Team2, Bobek
Team2, BobaFett


I tried join tables like 

UnitName_Teams.HierarchyPath = left(4,UnitName_Players.HierarchyPath)


but it crash on error

'Conversion failed when converting the varchar value '/42/48/63/' to data type int.'


How can I get children in this hierarchyPath 'data type' ? 
I can select players from one team with for example

Like '/42/%'


But how to do it for all Teams ?!
You need to 'walk the tree'.  This can be done using a recursive CTE, something like this:

USE tempdb
GO

CREATE TABLE dbo.Unit (
    id INT IDENTITY PRIMARY KEY,
    unitName        VARCHAR(20),
    hierarchyPath   hierarchyId UNIQUE,
    typeId          INT
    )
GO

CREATE TABLE dbo.UnitType (
    id      INT PRIMARY KEY,
    name    VARCHAR(20)
    )
GO

INSERT INTO dbo.Unit ( hierarchyPath, unitName, typeId )
VALUES 
    ( '/42/', 'Team1', 1 ),
    ( '/42/1/', 'John', 2 ),
    ( '/42/2/', 'Joe', 2 ),
    ( '/42/3/', 'Johana', 2 ),

    ( '/43/', 'Team2', 1 ),
    ( '/43/1/', 'Bob', 2 ),
    ( '/43/2/', 'Bobek', 2 ),
    ( '/43/3/', 'wBobaFett', 2 )

INSERT INTO dbo.UnitType ( Id, name )
VALUES ( 1, 'Team' ), ( 2, 'Name' )
GO

--SELECT *, hierarchyPath.ToString() hierarchyPath_string
--FROM dbo.Unit

;WITH cte AS
(
SELECT 1 xlevel, Id, hierarchyPath, unitName AS teamName, CAST( '' AS VARCHAR(20) ) unitName
FROM dbo.Unit
WHERE typeId = ( SELECT Id FROM dbo.UnitType WHERE name = 'Team' )

UNION ALL

SELECT xlevel + 1, u.Id, u.hierarchyPath, c.teamName, u.unitName
FROM cte c
    INNER JOIN dbo.Unit u ON u.hierarchyPath.GetAncestor (1) = c.hierarchyPath

)
SELECT teamName, unitName
FROM cte
WHERE xlevel &gt; 1
ORDER BY teamName, unitName
GO

DROP TABLE dbo.Unit
DROP TABLE dbo.UnitType
GO


HTH