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.

  1. Before you begin:  Limitations and Restrictions

  2. To process special characters:  Encoding an Identifier, Decoding an Identifier

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.

Limitations and Restrictions

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:

Character\/:%<>*?[]|
Hexadecimal Encoding%5C%2F%3A%25%3C%3E%2A%3F%5B%5D%7C

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

Examples (Encoding)

This example specifies the encoded version of the ":" character (%3A):

Set-Location Table%3ATest  

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.

Examples (Decoding)

This example returns “Table:Test”:

Decode-SqlName "Table%3ATest"  

SQL Server Identifiers in PowerShell
SQL Server PowerShell Provider
SQL Server PowerShell

Community Additions

ADD
Show: