Skip to content

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

Latest commit

 

History

History
82 lines (61 loc) · 3.7 KB

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

File metadata and controls

82 lines (61 loc) · 3.7 KB
title Encode and Decode SQL Server Identifiers | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-non-specified
ms.prod_service ssms
ms.service
ms.component scripting
ms.reviewer
ms.suite sql
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
ms.assetid bb9fe0d3-e432-42d3-b324-64dc908b544a
caps.latest.revision 7
author JennieHubbard
ms.author jhubbard
manager jhubbard
ms.workload Inactive

Encode and Decode SQL Server Identifiers

[!INCLUDEappliesto-ss-asdb-asdw-pdw-md] 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

Before You Begin

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

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

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