Escapes special characters in texts and returns text with escaped characters. STRING_ESCAPE is a deterministic function, which means it always return the same result any time they are called with a specific set of input values and given the same state of the database.
This is introduced in SQL Server 2016.
As per the documentation –
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 |
Syntax:
STRING_ESCAPE(expression, rules)
- expression – a character string with special characters to escape.
rules – the expression’s rules; presently, only the value ‘json’ is supported.
Examples:
SELECT STRING_ESCAPE(‘Hello /””/ W orld’, ‘json’) AS EscapedText;
Output:
Hello \/\”\”\/\t\tW\torld
SELECT STRING_ESCAPE(‘Hello /””/ W orld’,
‘json’) AS EscapedText;
Output:
Hello \/\”\”\/\t\tW\torld
SELECT STRING_ESCAPE(‘Hello
W orld’,
‘json’) AS EscapedText;
Output:
Hello \r\nW\torld
I hope you’ve found this information to be helpful.
Please join our mailing list to receive more interesting information.
Big Data and SQL 
4 comments