Escape Special Characters Using STRING_ESCAPE

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 ,

Escape Special Characters Using STRING_ESCAPE

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

Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.