In this SQL Server tutorial, I will teach about the STRING_ESCAPE function in SQL Server, where you will understand how to escape the special characters in the string.
I will explain the syntax you can use in your query to escape the special or control characters and what kind of parameter this function requires. Then, you will apply the STRING_ESCAPE() function on the string containing special characters to escape these characters in the string.
Finally, you will understand how to use the STRING_ESCAPE() function on the table columns and where and in what scenarios this function can be useful.
STRING_ESCAPE Function in SQL Server
The STRING_ESCAPE() function in SQL Server escapes the special characters in a text and returns the new text (string) with escaped characters. This function was introduced in the SQL Server 2016 and only supports JSON data. So, that means it works with JSON-type values.
The syntax is given below.
STRING_ESCAPE(expression, type)Where,
- STRING_ESCAPE(): Function that escapes the special characters in the given expression.
- expression: It can be text or string, or the value of type nvarchar that needs to be escaped.
- type: The type of the escaping rule that will applied to the expression. Currently, SQL Server supports only the JSON type.
The return type of the STRING_ESCAPE() is nvarchar(max) with the text and control characters.
The following special characters can be escaped by the STRING_ESCAPE() function:
| Encoded squence | Special Characters |
| \” | Quotation Mark |
| \/ | Solidus |
| \\ | Reverse Solidus |
| \f | Form Feed |
| \r | Carriage Return |
| \b | Backspace |
| \t | Horizontal Tab |
| \n | New Line |
| Encoded sequence | Control Characters |
| \u0000 | CHAR(0) |
| \u0001 | CHAR(1) |
| … | … |
| \u00f1 | CHAR(31) |
Let’s take an example. Suppose you have the string ‘James said, “He lives in the United States”‘, as you can see, this string contains a quotation mark (“). To escape this quotation, make use of the below query.
SELECT STRING_ESCAPE('James said, "He lives in the United States"', 'json');
In the output of the above query, the double quotes around the string “He lives in the United States” are correctly escaped.
However, the result included backslashes (\) before the double quotes to ensure that it can be safely included in a JSON object or array without breaking the format. For example \”He lives in the United States\”, look in the output of the above query.
This is how to escape special characters using the STRING_ESCAPE function in SQL Server.
STRING_ESCAPE Function in SQL Server on Table Column
In real life, if you are a database developer, you often encounter data in the table column containing a string containing these special characters that must be escaped when sending data from the database to the application.
For example, you have a CustomerData table with columns ID and Comments, as shown below.
As you can see, customer comments contain special characters. While generating a JSON output from the above data, unescaped characters in the Comments column could break the JSON structure. So here, you can use the STRING_ESCAPE() function to prevent this.
For that, use the below query.
SELECT STRING_ESCAPE(Comments, 'json') AS EscapedComments
FROM CustomerData;
After executing the above query, the result set contains the escaped version of all the comments in the Comments column from the CustomerData table to ensure that special comments are properly escaped for use in the JSON context.
- This STRING_ESCAPE() function is helpful in many scenarios, such as generating the JSON data when you create JSON string directly from the SQL Server for use in the APIs, web applications, etc that ingest JSON, the STRING_ESCAPE() makes sure that special characters and control characters don’t break the JSON format.
- In web development, it is essential to escape these special characters because it prevents injection attacks, and preventing this is a crucial practice.
- Also valuable for data transformation and report generation, when generating reports in the JSON and XML format, STRING_ESCAPE() helps prepare the data by escaping any special characters in string fields.
- When you use the SQL Server as a backend for API (Application Programming Interface), in this case, using the STRING_ESCAPE() function confirms that the JSON responses generated by SQL queries are accurately formatted.
This is how to use the STRING_ESCAPE function in SQL Server to escape the special characters in table columns, and it can be useful in various scenarios.
Conclusion
In this SQL Server tutorial, you have escaped the special characters in the string using the STRING_ESCAPE() function in SQL Server. You applied the STRING_ESCAPE() function on the table column containing the customer’s comments and escaped those special characters in the comments. Also, you learned the usefulness of the STRING_ESCAPE() function.
You may like to read:
- SQL Server STRING_AGG Function
- STRING_SPLIT Function in SQL Server
- SQL Server Escape Single Quote in String
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.