Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics (serverless SQL pool only)
This article describes how the FOR JSON
clause of a SQL Server SELECT
statement escapes special characters and represents control characters in the JSON output.
Important
This article describes the built-in support for JSON in Microsoft SQL Server. For general information about escaping and encoding in JSON, see Section 2.5 of the JSON RFC.
If the source data contains special characters, the FOR JSON
clause escapes them in the JSON output with \
, as shown in the following table. This escaping occurs both in the names of properties and in their values.
Special character | Escaped output |
---|---|
Quotation mark (" ) |
\" |
Backslash (\ ) |
\\ |
Slash (/ ) |
\/ |
Backspace | \b |
Form feed | \f |
New line | \n |
Carriage return | \r |
Horizontal tab | \t |
If the source data contains control characters, the FOR JSON
clause encodes them in the JSON output in \u<code>
format, as shown in the following table.
Control character | Encoded output |
---|---|
CHAR(0) | \u0000 |
CHAR(1) | \u0001 |
... | ... |
CHAR(31) | \u001f |
Here's an example of the FOR JSON
output for source data that includes both special characters and control characters.
Query:
SELECT 'VALUE\ /
"' AS [KEY\/"],
CHAR(0) AS '0',
CHAR(1) AS '1',
CHAR(31) AS '31'
FOR JSON PATH;
Result:
[
{
"KEY\\\/\"": "VALUE\\ \/\r\n \"",
"0": "\u0000",
"1": "\u0001",
"31": "\u001f"
}
]
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Format alphanumeric data for presentation in C# - Training
Explore basic methods in C# to format alphanumeric data.