What is STRING_ESCAPE?
You have already learned about some functions for manipulating strings. STRING_ESCAPE also fits into this series. The STRING_ESCAPE function is a function that replaces special characters in a string so that this string can be used elsewhere. A classic example of string escaping is that to avoid SQL injection attacks, you should not pass strings directly into your application, but always either use SqlParameter or at least replace the single quote with a double quote to avoid names like this:
';DROP TABLE User;
However, STRING_ESCAPE is not about escaping strings for secure use in SQL Server, but about escaping them when they are to be passed from SQL Server to another application.
How do you use STRING_ESCAPE?
The number of data exchange formats is probably almost infinite. However, JSON has become the format of choice in many areas in recent years. So it’s not surprising that when Microsoft introduced the STRING_ESCAPE feature in SQL Server 2016, they first had the export to JSON in mind. The STRING_ESCAPEfunction is passed two arguments: the text to be escaped and the desired output type, i.e. for which target format to escape. The latter, however, only supports the value “json” since the introduction of the function, so currently you can only use STRING_ESCAPE to prepare text for use in a JSON attribute. Let’s have a look at how this works. Special characters in JSON are for example quotes and slashes. Let’s call the STRING_ESCAPEfunction once:
SELECT STRING_ESCAPE('"', 'json')
Here we get back the quotation mark escaped by the backslash. Of course, this works similarly for slashes (/) and backslashes ():
SELECT STRING_ESCAPE('/"\', 'json')
But also the ASCII control characters CHAR(0) to CHAR(31) (a detailed list of these characters can be found here for example: https://de.wikipedia.org/wiki/Steuerzeichen) must be escaped.
A list of these characters and their “translations” can be generated as follows:
DROP TABLE IF EXISTS #Characters
CREATE TABLE #Characters (
[Char] varchar(20)
,[Escaped] varchar(20)
)
DECLARE @CharNo int = 0
WHILE @CharNo < 32
BEGIN
INSERT INTO #Characters ([Char], [Escaped])
VALUES ('CHAR('+CAST(@CharNo as varchar(2))+')', STRING_ESCAPE(CHAR(@CharNo), 'json'))
SELECT @Charno = @CharNo+1
END
SELECT [Char], [Escaped] from #Characters
Here you can see, for example, that a CHAR(13) corresponds to a "\r", that is, a return, a new line, and a CHAR(10) corresponds to a "\n", that is, a line break.
Of course, if you want to export text from a content management system for further processing, you could replace all these characters in a very long nested REPLACE statement, or you could simply use STRING_ESCAPEand get the text ready for distribution in JSON format.
The clever ninja knows the weapons at his disposal and knows how to use them when appropriate.
