MS SQL STRING_ESCAPE introduced in MS SQL 2016 escapes special characters in texts and returns text with escaped characters.

Syntax:

STRING_ESCAPE(text, type)

• Text - Is a string data type representing the object that should be escaped
• Type - Escaping rules that will be applied

The function returns the escaped text in nvarchar(max) data type.

Currently the only supported type of escaping rule is JSON.

JSON escaping rules:

• Quotation mark (")  -> \"
• Reverse solidus (\) -> \\
• Solidus (/) -> \/
• Backspace -> \b
• Form feed -> \f
• New line -> \n
• Carriage return -> \r
• Horizontal tab -> \t
• Control characters (0-31) -> \u<code> (e.g. CHAR(0) -> \u0000)

Let’s see some examples.

Example:

DECLARE @InputValue NVARCHAR(MAX) = N'
https://blog.matesic.info
C:\\MS SQL STRING ESCAPE
TAB:	';
SELECT STRING_ESCAPE(@InputValue,'JSON') AS Result;

SELECT STRING_ESCAPE(CHAR(7),'JSON') AS Result;

MS SQL STRING_ESCAPE returns NULL if the input value is NULL.

Example:

SELECT STRING_ESCAPE(NULL,'JSON') AS Result;

Let's try to create JSON objects based on real data from a table.

Example:

USE [WideWorldImporters];
SELECT 
	FORMATMESSAGE('{ "CustomerID": %d,"Name": "%s" }', [CustomerID], STRING_ESCAPE([CustomerName],'json')) AS "JSON Object"
FROM 
	[Sales].[Customers]  

 *MS SQL Server sample database "WideWorldImporters" can be downloaded at: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (680.00 bytes)