Skip to content

Latest commit

 

History

History
73 lines (48 loc) · 3.16 KB

encode-and-decode-sql-server-identifiers.md

File metadata and controls

73 lines (48 loc) · 3.16 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic
Encode and Decode SQL Server Identifiers
Some characters that can appear in SQL Server-delimited identifiers are not supported in Windows PowerShell paths. Learn how to include them by representing them with their hexadecimal values.
markingmyname
maghan
matteot, drskwier
10/14/2020
sql
sql-server-powershell
conceptual

Encode and Decode SQL Server Identifiers

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]

SQL Server-delimited identifiers sometimes contain characters not supported in Windows PowerShell paths. These characters can be specified by encoding their hexadecimal values.

[!INCLUDE sql-server-powershell-version]

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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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. 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

Encoding 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-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")

Decoding 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 represented by the encoding.

Examples (Decoding)

This example returns "Table:Test":

Decode-SqlName "Table%3ATest"

See Also