The STRING_ESCAPE function is a scalar function and escapes special characters in input text according to the given formatting rules. It returns input text with escaped characters. The function accepts the following two input arguments:

• Text: This is an expression of any non-deprecated string data type.
• Type: This argument must have the JSON value, since SQL Server 2016 currently supports only JSON as the formatting type.

The return type of the function is nvarchar(max). The STRING_ESCAPE function is a deterministic function; it always returns the same result for the same input parameters.

JSON's escaping rules are defined in the ECMA 404 standard specification. The following table provides the list of characters that must be escaped according to this specification and their JSON conform representation:

JSON escaping rules

In addition to this, all control characters with character codes in the range 0–31 need to be escaped too. In JSON output, they are represented in the following format: u<code>. Thus, the control character CHAR(0) is escaped as u0000, while CHAR(31) is represented by u001f.

The following several examples will be used to demonstrate how this function works. Suppose you need to escape the following string: ac/de"f. According to JSON's escaping rules, three characters should be escaped: back slash, solidus, and double quote. You can check it by calling the STRING_ESCAPE function for this string as the input argument:

SELECT STRING_ESCAPE('ac/de"f','JSON') AS escaped_input; 

Here is the result returned by the function:

escaped_input
-------------
a\bc/de"f

The following example demonstrates the escape of the control characters with the code 0, 4, and 31:

SELECT  
  STRING_ESCAPE(CHAR(0), 'JSON') AS escaped_char0,  
  STRING_ESCAPE(CHAR(4), 'JSON') AS escaped_char4,  
  STRING_ESCAPE(CHAR(31), 'JSON') AS escaped_char31; 

This function call produces the following output:

escaped_char0      escaped_char4      escaped_char31
-------------      -------------      --------------
u0000             u0004             u001f

The next example shows that the horizontal tab represented by the string and code is escaped with the same sequence:

SELECT  
  STRING_ESCAPE(CHAR(9), 'JSON') AS escaped_tab1,  
  STRING_ESCAPE('    ', 'JSON') AS escaped_tab2; 

Both statements resulted in a sequence:

escaped_tab1    escaped_tab2
------------    --------------
	               	

 The function returns a NULL value if the input string is not provided. To check this, run the following code:

DECLARE @input AS NVARCHAR(20) = NULL; 
SELECT STRING_ESCAPE(@input, 'JSON') AS escaped_input; 

You will get the expected result:

escaped_input
--------------
NULL

Escaping occurs both in the names of properties and in their values. Consider the following example, where one of the keys in the JSON input string contains a special character:

SELECT STRING_ESCAPE(N'key:1, id:4', 'JSON') AS escaped_input;

Here is the output:

escaped_input
---------------
key:1, i\d:4

The STRING_ESCAPE function is internally used by the FOR JSON clause to automatically escape special characters and represents control characters in the JSON output. It can also be used for formatting paths, especially if you need to run it on UNIX systems (which is happening with R integration and SQL Server on Linux). Sometimes, a forward slash or backslash needs to be doubled, and this function is perfect when preparing code for Unix or CMD commands; a backslash needs to be doubled and converted to a forward slash. Unlike the STRING_SPLIT function, this function is available in a SQL Server 2016 database, even in old database compatibility levels.