Export (0) Print
Expand All

ToString (Database Engine)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

-- 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  ( ) 

SQL Server return type: nvarchar(4000)

CLR return type: String

Returns the logical location in the hierarchy. For example, /2/1/ represents the fourth row (Microsoft SQL Server) 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

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:

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

Here is the result set.

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 ;

Here is the result set.

hierarchyidRepresentation StringRepresentation

------------------------- -----------------------

0x5ADE /1/1/3/

C. CLR example

The following code snippet calls the ToString() method:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft