In my earlier article, I have given you couple of methods to escape single quote in string. Here we will see escaping some of the other special characters like double quote, forward and reverse slash, backspace, etc… using the system function STRING_ESCAPE available in SQL Server 2016 and higher versions. Let’s see how to escape special characters using STRING_ESCAPE.
Syntax
The syntax for using STRING_ESCAPE is:
STRING_ESCAPE(text, type)
Here text represents a nvarchar string having the special characters to be escaped. Type represents the escape rule to be used. Currently this function supports only JSON type. You have to specify ‘json’ for type. This will return the text as nvarchar(max) with the escaped special characters.
Usage
Here is and example of how to use the STRING_ESCAPE function:
/* Example for using STRING_ESCAPE */
SELECT STRING_ESCAPE('Double quote:" , Forward slash:/ ,
Reverse slash:\ , Tab: , Carriage Return & New line:
','json')
/* Result */
Double quote:\" , Forward slash:\/ , Reverse slash:\\ , Tab:\t , New line:\r\n ,
Special Characters Escaped By STRING_ESCAPE
As of now, this function only escapes the JSON special characters and control characters. Here is the list of special characters:
Special Characters |
Escapes As |
|---|---|
| Double Quote (“) | \” |
| Forward Slash (/) | \/ |
| Reverse Slash (\) | \\ |
| Backspace | \b |
| Form feed | \f |
| Carriage Return | \r |
| Line Feed | \n |
| Tab | \t |
Reference
- About STRING_ESCAPE at Microsoft docs.