By: Daniel Calbimonte
The STRING_ESCAPE returns special characters that are escaped so they can be part of the string. The function is valid in SQL Server 2016 and later.
Syntax
STRING_ESCAPE(expression,rules)
Parameters
- expression - this is a string of character with special characters to escape.
- rules – the rules applied to the expression, currently the only value supported is 'json'.
The following characters can be escaped:
| Character | Escaped Value |
|---|---|
| " | \" |
| \ | \\ |
| / | \/ |
| Backspace | \b |
| Form feed | \f |
| New line | /n |
| Carriage return | \r |
| Horizontal tab | \t |
Simple STRING_ESCAPE Example
The following example shows how to escape a message with special characters.
SELECT STRING_ESCAPE('/hello everybody
How are you \','json') as msg
The following characters are escaped:
- forward slash
- carriage return
- new line
- backslash
Example with JSON data
The following uses a variable with JSON data and special characters.
declare @jsonvar VARCHAR(max)=
'[
{
"firstname": "Sarah",
"lastname": "Connor",
"skills": ["guns\", "fight "]
},
{
"firstname": "John",
"lastname": "Connor"
"skills": ["hacking
\/"]
}
]'
If we select the variable, we will see the values escaped.
SELECT STRING_ESCAPE(@jsonvar,'json')
Show Special Characters Escaped
The following example will show some characters and the escaped values.
WITH sequence(count) AS
(
SELECT 0
UNION ALL
SELECT count+1 FROM sequence WHERE count < 17
)
SELECT CONCAT('CHAR(',count,')') controlchar,STRING_ESCAPE(char([count]),'JSON') escapedcharacters FROM sequence;
