Skip to content

Latest commit

 

History

History
110 lines (90 loc) · 3.74 KB

File metadata and controls

110 lines (90 loc) · 3.74 KB
title STRING_ESCAPE (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 02/25/2016
ms.prod sql
ms.prod_service database-engine, sql-database
ms.service
ms.component t-sql|functions
ms.reviewer
ms.suite sql
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
STRING_ESCAPE
STRING_ESCAPE_TSQL
dev_langs
TSQL
helpviewer_keywords
STRING_ESCAPE function
ms.assetid 2163bc7a-3816-4304-9c40-8954804f5465
caps.latest.revision 11
author edmacauley
ms.author edmaca
manager craigg
ms.workload Active

STRING_ESCAPE (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]

Escapes special characters in texts and returns text with escaped characters. STRING_ESCAPE is a deterministic function.

Topic link icon Transact-SQL Syntax Conventions

Syntax

STRING_ESCAPE( text , type )  

Arguments

text
Is a nvarcharexpression expression representing the object that should be escaped.

type
Escaping rules that will be applied. Currently the value supported is 'json'.

Return Types

nvarchar(max) text with escaped special and control characters. Currently STRING_ESCAPE can only escape JSON special characters shown in the following tables.

Special character Encoded sequence
Quotation mark (") \"
Reverse solidus (\) \|
Solidus (/) \/
Backspace \b
Form feed \f
New line \n
Carriage return \r
Horizontal tab \t
Control character Encoded sequence
CHAR(0) \u0000
CHAR(1) \u0001
... ...
CHAR(31) \u001f

Remarks

Examples

A. Escape text according to the JSON formatting rules

The following query escapes special characters using JSON rules and returns escaped text.

SELECT STRING_ESCAPE('\   /  
\\    "     ', 'json') AS escapedText;  

[!INCLUDEssResult]

escapedText  
-------------------------------------------------------------  
\\\t\/\n\\\\\t\"\t

B. Format JSON object

The following query creates JSON text from number and string variables, and escapes any special JSON character in variables.

SET @json = FORMATMESSAGE('{ "id": %d,"name": "%s", "surname": "%s" }',   
    17, STRING_ESCAPE(@name,'json'), STRING_ESCAPE(@surname,'json') );  

See Also

CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)
REVERSE (Transact-SQL)
STRING_AGG (Transact-SQL)
STUFF (Transact-SQL)
TRANSLATE (Transact-SQL)
String Functions (Transact-SQL)