Encode and Decode SQL Server Identifiers
Applies To: SQL Server 2016
SQL Server delimited identifiers sometimes contain characters not supported in Windows PowerShell paths. These characters can be specified by encoding their hexadecimal values.
Before you begin: Limitations and Restrictions
Characters that are not supported in Windows PowerShell path names can be represented, or encoded, as the "%" character followed by the hexadecimal value for the bit pattern that represents the character, as in "%xx". Encoding can always be used to handle characters that are not supported in Windows PowerShell paths.
The Encode-SqlName cmdlet takes as input a SQL Server identifier. It outputs a string with all the characters that are not supported by the Windows PowerShell language encoded with "%xx". The Decode-SqlName cmdlet takes as input an encoded SQL Server identifier and returns the original identifier.
The Encode-Sqlname and Decode-Sqlname cmdlets only encode or decode the characters that are allowed in SQL Server delimited identifiers, but are not supported in PowerShell paths. These are the characters encoded by Encode-SqlName and decoded by Decode-SqlName:
To encode a SQL Server identifier in a PowerShell path
Use one of two methods to encode a SQL Server identifier:
Specify the hexadecimal code for the unsupported character using the syntax %XX, where XX is the hexadecimal code.
Pass the identifier as a quoted string to the Encode-Sqlname cmdlet
This example specifies the encoded version of the ":" character (%3A):
Alternatively, you can use Encode-SqlName to build a name supported by Windows PowerShell:
Set-Location (Encode-SqlName "Table:Test")
To decode a SQL Server identifier from a PowerShell path
Use the Decode-Sqlname cmdlet to replace the hexadecimal encodings with the characters represented by the encoding.
This example returns “Table:Test”: