Encode and decode SQL Server identifiers
SQL Server-delimited identifiers sometimes contain characters that are not supported in Windows PowerShell paths. These characters can be specified by encoding their hexadecimal values.
Note
There are two SQL Server PowerShell modules; SqlServer and SQLPS.
The SqlServer module is the current PowerShell module to use.
The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.
The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.
Install the SqlServer module from the PowerShell Gallery.
For more information, visit SQL Server PowerShell.
Characters 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 an SQL Server identifier as input and outputs a string with all the characters not supported by the Windows PowerShell language encoded with %xx. The Decode-SqlName cmdlet takes an encoded SQL Server identifier as input and returns the original identifier.
Limitations and restrictions
The Encode-Sqlname and Decode-Sqlname cmdlets only encode or decode the characters allowed in SQL Server-delimited identifiers but not supported in PowerShell paths. The following 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 |
Encode an Identifier
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-Sqlnamecmdlet
Example (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")
Decode an identifier
To decode a SQL Server identifier from a PowerShell path, use the Decode-Sqlname cmdlet to replace the hexadecimal encodings with the characters the encoding represents.
Example (decoding)
This example returns "Table:Test":
Decode-SqlName "Table%3ATest"