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
Syntax
STRING_ESCAPE( text , type )
Arguments
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'
.
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;
Here is the result set.
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
Tilbakemeldinger
https://aka.ms/ContentUserFeedback.
Kommer snart: Gjennom 2024 faser vi ut GitHub Issues som tilbakemeldingsmekanisme for innhold, og erstatter det med et nytt system for tilbakemeldinger. Hvis du vil ha mer informasjon, kan du se:Send inn og vis tilbakemelding for