tostring-database-engine.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs
ToString (Database Engine)	ToString (Database Engine)	MikeRayMSFT	mikeray	10/05/2021	sql	t-sql	reference	ToString ToString_TSQL	ToString [Database Engine]	TSQL

ToString (Database Engine)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

Returns a string with the logical representation of this. ToString is called implicitly when a conversion from hierarchyid to a string type occurs. Acts as the opposite of Parse (Database Engine).

Syntax

-- Transact-SQL syntax
node.ToString  ( )
-- This is functionally equivalent to the following syntax  
-- which implicitly calls ToString():  
CAST(node AS nvarchar(4000))  

-- CLR syntax
string ToString  ( )

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Return types

SQL Server return type:nvarchar(4000)

CLR return type:String

Remarks

Returns the logical location in the hierarchy. For example, /2/1/ represents the fourth row ( [!INCLUDEmsCoName] [!INCLUDEssNoVersion]) in the following hierarchical structure of a file system:

/        C:\  
/1/      C:\Database Files  
/2/      C:\Program Files  
/2/1/    C:\Program Files\Microsoft SQL Server  
/2/2/    C:\Program Files\Microsoft Visual Studio  
/3/      C:\Windows  

Examples

A. Transact-SQL example in a table

The following example returns both the OrgNode column as both the hierarchyid data type and in the more readable string format:

SELECT OrgNode,  
OrgNode.ToString() AS Node  
FROM HumanResources.EmployeeDemo  
ORDER BY OrgNode ;  
GO  

[!INCLUDEssResult]

OrgNode   Node  
0x        /  
0x58      /1/  
0x5AC0    /1/1/  
0x5B40    /1/2/  
0x5BC0    /1/3/  
0x5C20    /1/4/  
...  

B. Converting Transact-SQL values without a table

The following code example uses ToString to convert a hierarchyid value to a string, and Parse to convert a string value to a hierarchyid.

DECLARE @StringValue AS nvarchar(4000), @hierarchyidValue AS hierarchyid  
SET @StringValue = '/1/1/3/'  
SET @hierarchyidValue = 0x5ADE  
  
SELECT hierarchyid::Parse(@StringValue) AS hierarchyidRepresentation,  
@hierarchyidValue.ToString() AS StringRepresentation ;
GO  

[!INCLUDEssResult]

hierarchyidRepresentation    StringRepresentation
-------------------------    -----------------------
0x5ADE                       /1/1/3/

C. CLR example

The following code snippet calls the ToString() method:

this.ToString()  

See also

hierarchyid Data Type Method Reference
Hierarchical Data (SQL Server)
hierarchyid (Transact-SQL)