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;