G’day,
We observed in a previous installment that JSON uses the backslash character “\” as the escape character.
However, what happens if we actually want a backslash in our sting.
Well, We just escape that with another backslash
SELECT STRING_ESCAPE('\' , 'json');
Which gives (simply)
\\So when we see a JSON document like this
[
{
"Character": "\n"
},
{
"Character": "\r"
}
]
Then we might wonder
- Has something gone wrong?
- Is this valid JSON?
The answer would be that everything is fine and this is perfectly valid JSON.
Why?
Because \r and \n are both non-printable characters.
You’ll see this is valid if you use STRING_ESCAPE
SELECT STRING_ESCAPE(CHAR(10) , 'json') AS [Character]
UNION
SELECT STRING_ESCAPE(CHAR(13) , 'json');
Which gives
Char(10) – ASCII 10 – is the new line character, while CHAT(13) – ASCII 13 – is the carriage return character.
If you’d like to look at other examples of non printable characters then you can play with the STRING_EXCAPE and ASCII T-SQL functions
I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.
Have a great day
Cheers
Marty
Download Files
JSON, SQL Server and Esacpe Characters of the non-printable kind