Summary: in this tutorial, you will learn how to use the SQL Server STRING_ESCAPE() function to escape special characters in a string.
SQL Server STRING_ESCAPE() function overview
The STRING_ESCAPE() function escapes special characters in a string and returns the new string with escaped character. Currently, the STRING_ESCAPE() function only supports escaping JSON’s special characters.
The following shows the syntax of the STRING_ESCAPE() function:
STRING_ESCAPE(input_string, type)
Code language: SQL (Structured Query Language) (sql)The STRING_ESCAPE() accepts two arguments:
input_stringis an expression that resolves to a string to be escaped.typespecifies the escaping rules that will be applied. Currently,typeaccepts only'json'value.
The following tables list JSON special characters to be escaped:
| 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 |
SQL Server STRING_ESCAPE() function example
This example uses the STRING_ESCAPE() function to escape special characters using JSON rules and return a new string with escaped characters.
SELECT
STRING_ESCAPE('['' This is a special / "message" /'']', 'json') AS
escapedJson;
Code language: SQL (Structured Query Language) (sql)Here is the output:
escapedJson
--------------------------------------------
['\tThis is a special \/ \"message\" \/']
(1 row affected)
Code language: SQL (Structured Query Language) (sql)In this tutorial, you have learned how to use the SQL Server STRING_ESCAPE() function to escape special character using JSON rules and return the new string with escaped characters.
