STRING_ESCAPE (Transact-SQL)
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
Escapes special characters in texts and returns text with escaped characters. STRING_ESCAPE is a deterministic function, introduced in SQL Server 2016.
Transact-SQL syntax conventions
STRING_ESCAPE( text , type )
text
Is a nvarchar expression representing the object that should be escaped.
type
Escaping rules that will be applied. Currently the value supported is 'json'
.
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 |
The following query escapes special characters using JSON rules and returns escaped text.
SELECT STRING_ESCAPE('\ /
\\ " ', 'json') AS escapedText;
Here's the result set.
escapedText
-------------------------------------------------------------
\\\t\/\n\\\\\t\"\t
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') );