Skip to content

how-for-json-escapes-special-characters-and-control-characters-sql-server.md

Latest commit

 

History

History
84 lines (71 loc) · 3 KB

how-for-json-escapes-special-characters-and-control-characters-sql-server.md

File metadata and controls

84 lines (71 loc) · 3 KB
title How FOR JSON escapes special characters and control characters (SQL Server) | Microsoft Docs
ms.custom
SQL2016_New_Updated
ms.date 03/16/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
dbe-json
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
FOR JSON, special characters
ms.assetid 4ba90025-5a09-4f0a-836a-54c886324530
caps.latest.revision 16
author douglaslMS
ms.author douglasl
manager craigg

How FOR JSON escapes special characters and control characters (SQL Server)

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx_md]

This topic 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 page describes the built-in support for JSON in Microsoft SQL Server. For general info about escaping and encoding in JSON, see Section 2.5 of the JSON RFC - http://www.ietf.org/rfc/rfc4627.txt.

Escaping of special characters

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

Control characters

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

Example

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\\\t\/\"": "VALUE\\\t\/\r\n\"",
	"0": "\u0000",
	"1": "\u0001",
	"31": "\u001f"
}

Learn more about the built-in JSON support in SQL Server

For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

See Also

Format Query Results as JSON with FOR JSON (SQL Server)
FOR Clause